Tables, constraints and safe ALTER TABLE

conceptThe concept

A primary key is unique and not null and gets a unique B-tree index. UNIQUE constraints also create an index. Foreign keys require the referenced columns to be unique; they do not create an index on the referencing side, which is a classic performance trap. CHECK constraints evaluate an expression per row, and table-level CHECKs can compare columns.

Foreign keys choose what happens when the parent row is deleted or its key changes: NO ACTION (default, error), RESTRICT, CASCADE, SET NULL or SET DEFAULT. Constraints can be DEFERRABLE INITIALLY DEFERRED to be checked at commit instead of per statement.

DDL is transactional: you can BEGIN, create and alter tables, test, and ROLLBACK. A few commands (like CREATE INDEX CONCURRENTLY and VACUUM) cannot run inside a transaction block.

whyWhy it matters

Application-level validation misses the batch import, the hotfix script and the second service. A CHECK constraint does not. The flip side is that adding a constraint to a big table scans it while holding a lock, so production changes need the NOT VALID pattern.

Use caseReal-world scenario

Adding a "total must be non-negative" rule to a 500 million row orders table: ADD CONSTRAINT ... NOT VALID takes a brief lock and only checks new rows; VALIDATE CONSTRAINT then scans existing rows under a lock that does not block reads or writes.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE TABLE coupons (
  code         text PRIMARY KEY,
  percent_off  integer NOT NULL CHECK (percent_off BETWEEN 1 AND 90),
  valid_from   date NOT NULL,
  valid_to     date NOT NULL,
  customer_id  integer REFERENCES customers (id) ON DELETE CASCADE,
  CONSTRAINT coupons_dates_ok CHECK (valid_to > valid_from)
);
INSERT INTO coupons VALUES ('WELCOME10', 10, '2025-01-01', '2025-12-31', NULL);
\d coupons
ROLLBACK;

BEGIN;
CREATE TABLE coupons (
  code text PRIMARY KEY,
  percent_off integer NOT NULL CHECK (percent_off BETWEEN 1 AND 90)
);
INSERT INTO coupons VALUES ('TOO-GOOD', 95);

BEGIN;
DELETE FROM customers WHERE id = 1;

BEGIN;
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';
ALTER TABLE orders ADD CONSTRAINT orders_total_nonneg CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_nonneg;
SELECT channel, count(*) FROM orders GROUP BY channel;
ROLLBACK;

internalsUnder the hood

Since PostgreSQL 11, ADD COLUMN ... DEFAULT constant is instant: the default is stored in the catalog and returned for old rows without rewriting the table. A volatile default such as clock_timestamp() or a type change still rewrites the whole table under an ACCESS EXCLUSIVE lock.

Avoid / preferDo this, not that

ALTER TABLE orders ADD CONSTRAINT total_not_negative CHECK (total >= 0);

ALTER TABLE orders ADD CONSTRAINT total_not_negative CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_not_negative;

ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT compute_region();

ALTER TABLE orders ADD COLUMN region text;
-- backfill in batches, then
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

PitfallsCommon mistakes

• Forgetting to index foreign-key columns. Deleting a parent row then scans the whole child table, and joins from parent to child cannot use an index.
• Adding a CHECK or FOREIGN KEY constraint to a large table in one step during business hours. Use NOT VALID, then VALIDATE CONSTRAINT.
• Running DDL without SET lock_timeout. If it queues behind a long query, every later query queues behind it (the locking lesson shows this).

LimitsWhen not to use it

Do not write triggers for rules a constraint can express. Foreign keys, CHECK, UNIQUE and exclusion constraints are declarative, concurrency-safe and visible to the planner; trigger-based checks are easy to get wrong under concurrency.

SecuritySecurity notes

Constraints are the last line of defence against buggy or malicious clients that bypass application validation. Keep the critical rules (ownership, non-negative balances, valid states) in the database.

ChallengeTry it yourself

BEGIN;
ALTER TABLE orders ADD CONSTRAINT delivered_has_total
  CHECK (status <> 'delivered' OR total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT delivered_has_total;
SELECT conname, convalidated FROM pg_constraint WHERE conname = 'delivered_has_total';
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Constraints
• ALTER TABLE