Add a CHECK Constraint to a Table That Has Data

You want the database to refuse negative stock. You write ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) and PostgreSQL refuses to add it, because a row in the table already breaks the rule. Or it adds the constraint, but locks the table while it reads every row. This page finds the bad rows, fixes them, and adds the rule in a way that does not stop a busy table.

Step 1: a table with a bad row

CREATE TABLE inventory (
  product_id integer PRIMARY KEY,
  on_hand    integer NOT NULL
);
INSERT INTO inventory VALUES (1, 40), (2, 0), (3, -3), (4, 12);

CREATE TABLE
INSERT 0 4

Product 3 has negative stock. Nobody can have minus three of something, so this is a data problem that came in before the rule existed.

Step 2: the plain command fails

ALTER TABLE inventory ADD CONSTRAINT on_hand_not_negative CHECK (on_hand >= 0);

ERROR:  check constraint "on_hand_not_negative" of relation "inventory" is violated by some row

The error says which constraint and which table. It does not tell you which rows. You have to look.

Step 3: find the rows that break the rule

Run the opposite of the rule as a query. Remember that a CHECK only fails on false, so rows where the value is NULL pass. Use NOT (rule), which is also unknown for NULL, to match what the constraint does.

SELECT * FROM inventory WHERE NOT (on_hand >= 0);

 product_id | on_hand
------------+---------
          3 |      -3
(1 row)

One row. Decide what the right value is before you change anything. Zero may be right, or the number may point to a real problem in whatever fed this table. This is a decision for a person.

Step 4: add it as NOT VALID

NOT VALID adds the rule for new and changed rows and does not check the old ones. It is quick, and from this moment no new bad data can come in.

ALTER TABLE inventory ADD CONSTRAINT on_hand_not_negative CHECK (on_hand >= 0) NOT VALID;
INSERT INTO inventory VALUES (5, -1);

ALTER TABLE
ERROR:  new row for relation "inventory" violates check constraint "on_hand_not_negative"
DETAIL:  Failing row contains (5, -1).

The new bad row is refused. The old one, product 3, is still there. The constraint exists, but it is not trusted yet:

SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'inventory'::regclass AND contype = 'c';

       conname        | convalidated
----------------------+--------------
 on_hand_not_negative | f
(1 row)

convalidated is false. The planner will not rely on the rule until it is validated.

Step 5: fix the data, then validate

UPDATE inventory SET on_hand = 0 WHERE on_hand < 0;
ALTER TABLE inventory VALIDATE CONSTRAINT on_hand_not_negative;
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'inventory'::regclass AND contype = 'c';

UPDATE 1
ALTER TABLE
       conname        | convalidated
----------------------+--------------
 on_hand_not_negative | t
(1 row)

VALIDATE CONSTRAINT reads the table and fails if any row still breaks the rule. When it succeeds, the constraint is fully in force.

Why two steps? The one-step ADD CONSTRAINT takes a strong lock on the table while it scans every row, and on a large table that blocks reads and writes for as long as the scan takes. NOT VALID takes the lock only for a moment. VALIDATE CONSTRAINT takes a weaker lock that lets reads and writes continue while it scans. The order is: add as NOT VALID, clean up the old rows, validate.

Related course: SQL Intermediate.