Constraints: Primary Key, Foreign Key, Unique, Check, Not Null

Constraints enforce data invariants regardless of which application writes the rows. PRIMARY KEY enforces uniqueness and non-nullness for the key; FOREIGN KEY enforces references; UNIQUE prevents duplicate values; CHECK enforces a boolean condition; NOT NULL requires a value. Defaults provide a value when an insert omits a column, but a default is not a substitute for a constraint.

Put critical rules in the database when possible. Application validation is still valuable for user-friendly error messages, but database constraints protect against bugs, scripts, concurrent requests, and other clients.

Use multiple constraints

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  team_id BIGINT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('member', 'admin')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, team_id)
);

💡 The composite primary key prevents a user from being added to the same team twice. The CHECK constraint limits role values to the supported set.

Key Takeaways

Database constraints protect integrity across all clients.
A composite primary key can represent a many-to-many relationship.
Defaults do not replace NOT NULL or CHECK constraints.

Knowledge Check

Which constraint can restrict a role column to a known set of allowed values?

Constraints: Primary Key, Foreign Key, Unique, Check, Not Null

STAY UPDATED WITH BYTES!