Constraints and Keys

Introduction

Constraints enforce data rules—unique emails, required fields, valid relationships between tables. This chapter covers PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK, and ON DELETE / ON UPDATE actions. PostgreSQL uses constraints heavily; compare with MySQL constraints and document modeling in MongoDB embedded vs referenced.

Prerequisites

• Databases, Schemas, and Types
• Tables users and posts in your practice database

PRIMARY KEY and IDENTITY

Every table should have a primary key—usually a surrogate id:

CREATE TABLE demo_pk (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  note TEXT NOT NULL
);

Code explanation:

• PRIMARY KEY implies NOT NULL and UNIQUE
• Choose stable, narrow keys—avoid natural keys that change (email as PK is usually a bad idea)

Composite primary key (junction tables):

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL,
  tag TEXT NOT NULL,
  PRIMARY KEY (post_id, tag)
);

UNIQUE, NOT NULL, DEFAULT

CREATE TABLE subscribers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  subscribed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT uk_subscribers_email UNIQUE (email)
);

Multiple NULLs in a UNIQUE column are allowed in PostgreSQL—unlike MySQL where one NULL is typical. Use NOT NULL on unique business keys like email.

CHECK Constraints

PostgreSQL has long supported CHECK—use them for domain rules:

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  stock INTEGER NOT NULL,
  CONSTRAINT chk_price_positive CHECK (price >= 0),
  CONSTRAINT chk_stock_non_negative CHECK (stock >= 0),
  CONSTRAINT chk_name_not_empty CHECK (char_length(name) > 0)
);

Invalid insert fails:

INSERT INTO products (name, price, stock) VALUES ('Bad', -1, 10);

Add CHECK to an existing table:

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

FOREIGN KEY Basics

Link posts.user_id to users.id if not already present:

ALTER TABLE posts
  ADD CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id) REFERENCES users (id);

Code explanation:

• Child user_id must match parent type; parent users.id must be PRIMARY KEY or UNIQUE
• PostgreSQL does not auto-index foreign key columns—add an index on posts(user_id) for join performance

Valid insert:

INSERT INTO posts (user_id, title, body)
VALUES (1, 'FK demo', 'Valid user reference.');

Invalid user_id fails:

INSERT INTO posts (user_id, title, body)
VALUES (99999, 'Orphan', 'No such user.');

ON DELETE and ON UPDATE Actions

Comments table with cascade:

Optional assignee with SET NULL:

CREATE TABLE tasks (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  assignee_id BIGINT,
  CONSTRAINT fk_tasks_assignee
    FOREIGN KEY (assignee_id) REFERENCES users (id)
    ON DELETE SET NULL
);

ON UPDATE CASCADE updates child FKs when parent PK changes—rare with IDENTITY keys; avoid updating primary keys in apps.

DEFERRABLE Constraints (Optional)

Complex bulk loads may need to validate FKs at commit time instead of each statement:

-- Replace immediate FK with deferrable version (one FK per column)
ALTER TABLE posts DROP CONSTRAINT IF EXISTS fk_posts_user;
 
ALTER TABLE posts
  ADD CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id) REFERENCES users (id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
SET CONSTRAINTS fk_posts_user DEFERRED;
-- Insert parent and child in either order within the same transaction
COMMIT;

Most apps use INITIALLY IMMEDIATE (the default)—know DEFERRABLE for migrations and ETL scripts.

Drop and Inspect Constraints

List constraints on a table:

-- psql
\d posts
 
-- SQL
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'posts'::regclass;

Drop a constraint:

ALTER TABLE posts DROP CONSTRAINT IF EXISTS fk_posts_user;

Drop and recreate when changing behavior:

ALTER TABLE comments DROP CONSTRAINT fk_comments_post;
 
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_post
  FOREIGN KEY (post_id) REFERENCES posts (id)
  ON DELETE CASCADE;

Naming Conventions

Consistent names make errors readable: violates foreign key constraint "fk_posts_user".

Relational vs Document Modeling

PostgreSQL gives you both strict relational rules and JSONB in the same database.

Practice Exercise

Add comments with cascade, products with CHECK, and verify failures:

INSERT INTO products (name, price, stock) VALUES ('Widget', 9.99, 100);
INSERT INTO products (name, price, stock) VALUES ('Bad', -5, 10);  -- fails CHECK
INSERT INTO comments (post_id, body) VALUES (99999, 'orphan');     -- fails FK

FAQ

Must every FK have an index?

Not required by PostgreSQL—but highly recommended on the referencing column (posts.user_id) for joins and cascades.

UNIQUE vs PRIMARY KEY?

PRIMARY KEY is the main row identifier—one per table. UNIQUE adds additional uniqueness rules.

Can CHECK reference other rows?

Not directly—use triggers or exclusion constraints for cross-row rules (advanced).

ON DELETE CASCADE danger?

Deleting one parent row can wipe many children—confirm intent in admin tools. Soft deletes (deleted_at) avoid accidental cascades.

NULL in UNIQUE email column?

PostgreSQL allows multiple NULL emails if NOT NULL is missing—always email TEXT NOT NULL for login tables.

How does this differ from MongoDB references?

MongoDB references are application-enforced unless you use transactions and careful validation—PostgreSQL FOREIGN KEY rejects orphans at the database level.