Postgres add constraints with Schema Diff Studio
A subscriptions table is loaded before and after it got the rules it was missing: two columns become NOT NULL, a CHECK on plan values and seat count, a CHECK across two date columns, and UNIQUE constraints on one and two columns. The comparison runs on load and lists each constraint, flagging the ones existing rows can break, verified in Postgres in your tab.
- Paste the table as it is now into Before and the same table with its constraints into After. Name your constraints (CONSTRAINT name CHECK ...) so the migration and the error messages use names you chose.
- Press Compare. The Changes tab lists each SET NOT NULL, Add check constraint and Add unique constraint, with a note on the ones that fail if existing rows violate them.
- Run the matching checks on production data first: SELECT count(*) FROM subscriptions WHERE plan IS NULL, and SELECT account_id, starts_on, count(*) FROM subscriptions GROUP BY 1, 2 HAVING count(*) > 1.
- Open Migration, read the order (column changes, then constraints), confirm Verified and copy the script. The .sql download and the rollback script are Pro.
What to know
Every one of these statements checks all existing rows, and each fails as a whole if one row breaks the rule. SET NOT NULL scans the table under an ACCESS EXCLUSIVE lock, so reads and writes wait. Since Postgres 12 the scan is skipped when a validated CHECK (account_id IS NOT NULL) constraint already proves the column has no NULLs, which gives a low-lock path for big tables: add that CHECK as NOT VALID, VALIDATE it, SET NOT NULL, then drop the CHECK.
CHECK constraints follow the same pattern. ALTER TABLE subscriptions ADD CONSTRAINT subscriptions_seats_check CHECK (seats > 0) NOT VALID is instant and enforces the rule for new and updated rows; ALTER TABLE ... VALIDATE CONSTRAINT then scans the old rows under a SHARE UPDATE EXCLUSIVE lock that does not block normal reads and writes. The generated script uses the plain form, which is correct for small tables and for verification; switch to the two-step form for large ones.
A UNIQUE constraint is backed by a unique index, and ADD CONSTRAINT ... UNIQUE builds that index while blocking writes. To avoid that on a busy table, build it first with CREATE UNIQUE INDEX CONCURRENTLY subscriptions_coupon_code_key ON subscriptions (coupon_code), outside a transaction, then attach it with ALTER TABLE subscriptions ADD CONSTRAINT subscriptions_coupon_code_key UNIQUE USING INDEX subscriptions_coupon_code_key, which is quick.
NULL is not equal to NULL in a unique constraint, so many subscriptions can have no coupon_code and the constraint still holds. If two NULLs should count as duplicates, Postgres 15 and later accept UNIQUE NULLS NOT DISTINCT. A two-column UNIQUE (account_id, starts_on) only rejects rows where both values match; its index also serves queries that filter on account_id alone, but not on starts_on alone.
Updated · Cosmovex
Questions
How do I add a NOT NULL constraint to a column that has data?
First make sure no row is NULL (UPDATE ... SET col = default WHERE col IS NULL), then ALTER TABLE ... ALTER COLUMN col SET NOT NULL. On a large table, add CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL: Postgres 12+ uses the validated check and skips the full scan.
What does NOT VALID do on a CHECK or foreign key constraint?
It adds the constraint without checking existing rows, so the ALTER is instant, and enforces it for every new or updated row. ALTER TABLE ... VALIDATE CONSTRAINT later checks the old rows while letting normal reads and writes continue.
How do I add a unique constraint without locking the table?
Create the unique index with CREATE UNIQUE INDEX CONCURRENTLY outside a transaction, then turn it into a constraint with ADD CONSTRAINT name UNIQUE USING INDEX name. Only the second step takes a strong lock, and it is short.
Why did ADD CONSTRAINT UNIQUE fail with "could not create unique index"?
Existing rows contain duplicates. Find them with GROUP BY on the constrained columns HAVING count(*) > 1, resolve them, and run the statement again. NULL values never count as duplicates unless the constraint says NULLS NOT DISTINCT.
Does the comparison catch a CHECK constraint whose expression changed?
Yes. Each schema runs in a real Postgres and the constraint definitions are read back from the catalog, so a changed expression, a renamed constraint or a dropped one each show as their own change, with DROP and ADD written in a safe order.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
