Constraints & Identity Columns

NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY and GENERATED identity — and the exact errors PostgreSQL returns.

Constraints are rules the database enforces on every write, no matter which application or person makes it. They are the last line of defence for data quality: a bug in the application cannot store an invalid row.

Defining constraints

Checking "email not taken" in the application, then inserting

Declare UNIQUE (email) and handle the unique_violation error (or use ON CONFLICT)

Two requests can both pass the check before either inserts. Only a database constraint is safe under concurrency.

Why are there gaps in identity columns?

Values are taken from a sequence that is not transactional: a value used by a failed or rolled-back insert is not returned. This keeps inserts fast and concurrent; ids are unique, not consecutive.

What does ON DELETE CASCADE do?

When the referenced (parent) row is deleted, the rows that reference it are deleted automatically.