PostgreSQL constraints: CHECK, UNIQUE, foreign keys, and more

Every Postgres constraint type, the exact error each one raises, and how to add constraints to a live table without a long lock.

Quick answer

Postgres constraints are rules the database enforces on every insert and update. A row that breaks one is rejected, whichever application, script, or person sent it.

SQL

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL
);

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL,
  price numeric(10,2) NOT NULL CHECK (price > 0),
  discount_price numeric(10,2),
  CONSTRAINT discount_below_price CHECK (discount_price < price)
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  status text NOT NULL DEFAULT 'pending'
);

PostgreSQL has six constraint types:

Constraint	What it guarantees
`NOT NULL`	The column always has a value
`CHECK`	A boolean expression is not false for the row
`UNIQUE`	No two rows share the same value (or combination of values)
`PRIMARY KEY`	`UNIQUE` plus `NOT NULL`, one per table
`FOREIGN KEY`	The value exists in another table
`EXCLUDE`	No two rows conflict under an operator you choose, such as overlapping ranges

Every example and error message below comes from PostgreSQL 18.6.

Column constraints vs table constraints

You can write a constraint next to a column or as a separate line in the table definition. CHECK (price > 0) above is a column constraint. CONSTRAINT discount_below_price CHECK (...) is a table constraint, which is required when the rule involves more than one column.

If you do not name a constraint, PostgreSQL generates a name such as products_price_check, customers_email_key, or orders_customer_id_fkey. Named constraints produce clearer errors and are easier to drop later, so name anything you might need to reference in a migration.

postgres=# \d products
                               Table "public.products"
     Column     |     Type      | Collation | Nullable |           Default
----------------+---------------+-----------+----------+------------------------------
 id             | bigint        |           | not null | generated always as identity
 sku            | text          |           | not null |
 price          | numeric(10,2) |           | not null |
 discount_price | numeric(10,2) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "discount_below_price" CHECK (discount_price < price)
    "products_price_check" CHECK (price > 0::numeric)

NOT NULL

SQL

INSERT INTO customers (email) VALUES ('bo@example.com');

ERROR:  null value in column "name" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (3, bo@example.com, null).

NOT NULL is the constraint you will use most. Make it the default for every column and drop it only when a missing value means something.

Since PostgreSQL 18, NOT NULL constraints are stored in pg_constraint like the others, with generated names such as items_qty_not_null. That means you can name one, and you can add one as NOT VALID (covered below).

CHECK constraints

A CHECK constraint takes any boolean expression over the row's own columns:

SQL

INSERT INTO products (sku, price) VALUES ('MUG-01', 0);

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, MUG-01, 0.00, null).

The multi-column rule works the same way:

SQL

INSERT INTO products (sku, price, discount_price) VALUES ('MUG-01', 10, 12);

ERROR:  new row for relation "products" violates check constraint "discount_below_price"
DETAIL:  Failing row contains (2, MUG-01, 10.00, 12.00).

CHECK passes when the expression is NULL

A check fails only when the expression is false. NULL > 0 is null, not false, so a null value passes:

SQL

CREATE TABLE t_chk (n int CHECK (n > 0));
INSERT INTO t_chk VALUES (NULL);   -- INSERT 0 1

That is why discount_price above can be left empty. If you want the value required and valid, combine NOT NULL with the CHECK.

CHECK cannot look at other rows or tables

SQL

CREATE TABLE c (n int CHECK (n IN (SELECT 1)));

ERROR:  cannot use subquery in check constraint

Rules that depend on other rows belong in a UNIQUE, FOREIGN KEY, or EXCLUDE constraint, or in a trigger. You can get a CHECK past this error by wrapping the query in a function, but PostgreSQL only evaluates it when the row itself changes, so later changes to the other table go unchecked.

You can try NOT NULL, CHECK, and UNIQUE below. MUG-01 already exists. The sandbox runs SQLite in your browser, so the error wording differs from PostgreSQL, but the rows that get rejected are the same.

⚡

Loading SQL environment...

UNIQUE constraints

SQL

INSERT INTO customers (email, name) VALUES ('ana@example.com', 'Ana again');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(ana@example.com) already exists.

A UNIQUE constraint is backed by a unique B-tree index, which PostgreSQL creates for you. Do not add a second index on the same column; the constraint's index already serves lookups. See the CREATE INDEX guide for when you need more.

For a rule across several columns, list them together. This allows a user in many teams but only once per team:

SQL

CREATE TABLE memberships (
  team_id int,
  user_id int,
  UNIQUE (team_id, user_id)
);

ERROR:  duplicate key value violates unique constraint "memberships_team_id_user_id_key"
DETAIL:  Key (team_id, user_id)=(1, 1) already exists.

NULLs are distinct by default

A plain UNIQUE column accepts any number of nulls, because null is not equal to null:

SQL

CREATE TABLE coupons (code text UNIQUE);
INSERT INTO coupons VALUES (NULL), (NULL);   -- INSERT 0 2

Since PostgreSQL 15 you can change that with NULLS NOT DISTINCT:

SQL

CREATE TABLE coupons2 (code text UNIQUE NULLS NOT DISTINCT);
INSERT INTO coupons2 VALUES (NULL);
INSERT INTO coupons2 VALUES (NULL);

ERROR:  duplicate key value violates unique constraint "coupons2_code_key"
DETAIL:  Key (code)=(null) already exists.

A unique constraint is also what INSERT ... ON CONFLICT targets. The ON CONFLICT guide covers upserts in detail.

PRIMARY KEY

A primary key is UNIQUE and NOT NULL together, and a table can have only one. PostgreSQL marks every primary key column NOT NULL, even in a composite key:

SQL

CREATE TABLE order_items (
  order_id bigint,
  line_no int,
  qty int NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO order_items VALUES (NULL, 1, 1);

ERROR:  null value in column "order_id" of relation "order_items" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 1).

Declaring two primary keys fails at CREATE TABLE:

ERROR:  multiple primary keys for table "twopk" are not allowed

Use bigint GENERATED ALWAYS AS IDENTITY for surrogate keys. The ALWAYS part stops clients from writing their own ids by accident:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

The CREATE TABLE guide compares identity columns with serial.

FOREIGN KEY constraints

A foreign key requires the value to exist in the referenced table:

SQL

INSERT INTO orders (customer_id) VALUES (999);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

It also protects the other direction. Deleting a customer who still has orders fails:

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

ON DELETE actions

The default, NO ACTION, rejects the delete as above. The other options change the child rows instead:

SQL

CREATE TABLE posts (
  id int PRIMARY KEY,
  author_id int REFERENCES authors (id) ON DELETE CASCADE
);
CREATE TABLE comments (
  id int PRIMARY KEY,
  post_id int REFERENCES posts (id) ON DELETE SET NULL
);

Deleting author 1 deleted their post, and the comment on that post stayed with post_id set to null:

 id | author_id
----+-----------
(0 rows)

 id  | post_id
-----+---------
 100 |
(1 row)

CASCADE can remove far more than you expect when chains of tables cascade into each other. Use it for rows that have no meaning without their parent, like order lines, and leave the default in place elsewhere.

Foreign key errors at creation time

The referenced columns must have a primary key or unique constraint:

ERROR:  there is no unique constraint matching given keys for referenced table "parent_nu"

And the column types must be compatible:

ERROR:  foreign key constraint "child_t_pid_fkey" cannot be implemented
DETAIL:  Key columns "pid" of the referencing table and "id" of the referenced table are of incompatible types: text and integer.

PostgreSQL does not index the referencing column for you. orders.customer_id has no index unless you create one, and without it every delete from customers scans orders to look for references.

EXCLUDE constraints

An exclusion constraint generalises UNIQUE: instead of "no two rows are equal", it says "no two rows match under these operators". The classic use is stopping double bookings:

SQL

CREATE EXTENSION btree_gist;

CREATE TABLE bookings (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id int NOT NULL,
  during tstzrange NOT NULL,
  CONSTRAINT no_double_booking
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO bookings (room_id, during) VALUES (101, '[2026-10-08 09:00, 2026-10-08 10:00)');
INSERT INTO bookings (room_id, during) VALUES (101, '[2026-10-08 10:00, 2026-10-08 11:00)');
INSERT INTO bookings (room_id, during) VALUES (102, '[2026-10-08 09:30, 2026-10-08 10:30)');
INSERT INTO bookings (room_id, during) VALUES (101, '[2026-10-08 09:30, 2026-10-08 10:30)');

The first three succeed. Back-to-back slots do not overlap because the ranges are half-open, and room 102 is a different room. The fourth overlaps room 101's first booking:

ERROR:  conflicting key value violates exclusion constraint "no_double_booking"
DETAIL:  Key (room_id, during)=(101, ["2026-10-08 09:30:00+00","2026-10-08 10:30:00+00")) conflicts with existing key (room_id, during)=(101, ["2026-10-08 09:00:00+00","2026-10-08 10:00:00+00")).

The btree_gist extension is what lets a GiST index compare plain integers with =. Without it:

ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

WITHOUT OVERLAPS in PostgreSQL 18

PostgreSQL 18 adds a shorter form for the same idea on primary and unique keys:

SQL

CREATE TABLE room_rates (
  room_id int,
  valid_during daterange,
  rate numeric NOT NULL,
  PRIMARY KEY (room_id, valid_during WITHOUT OVERLAPS)
);
INSERT INTO room_rates VALUES (101, '[2026-01-01,2026-07-01)', 120);
INSERT INTO room_rates VALUES (101, '[2026-06-01,2027-01-01)', 140);

ERROR:  conflicting key value violates exclusion constraint "room_rates_pkey"
DETAIL:  Key (room_id, valid_during)=(101, [2026-06-01,2027-01-01)) conflicts with existing key (room_id, valid_during)=(101, [2026-01-01,2026-07-01)).

It is still an exclusion constraint underneath, so it needs btree_gist for the int column too.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

Adding constraints to an existing table

The ALTER TABLE guide covers the general syntax. For constraints:

SQL

ALTER TABLE items ADD CONSTRAINT items_qty_check CHECK (qty >= 0);
ALTER TABLE items DROP CONSTRAINT items_qty_check;
ALTER TABLE items DROP CONSTRAINT IF EXISTS items_qty_check;
ALTER TABLE items RENAME CONSTRAINT items_sku_key TO items_sku_unique;

Adding a constraint to a table that already holds bad data fails:

ERROR:  check constraint "balance_nonneg" of relation "accounts" is violated by some row

NOT VALID and VALIDATE CONSTRAINT

On a large table, a plain ADD CONSTRAINT scans every row while holding an ACCESS EXCLUSIVE lock that blocks reads and writes. Split it in two:

SQL

ALTER TABLE accounts
  ADD CONSTRAINT balance_nonneg CHECK (balance >= 0) NOT VALID;

This returns immediately without checking existing rows, even though row 3 has a balance of -5. New writes are checked straight away, including updates to old rows:

SQL

UPDATE accounts SET balance = balance + 1 WHERE id = 3;

ERROR:  new row for relation "accounts" violates check constraint "balance_nonneg"
DETAIL:  Failing row contains (3, -4).

\d shows the state:

Check constraints:
    "balance_nonneg" CHECK (balance >= 0::numeric) NOT VALID

Fix the old rows, then validate:

SQL

UPDATE accounts SET balance = 0 WHERE balance < 0;
ALTER TABLE accounts VALIDATE CONSTRAINT balance_nonneg;

Validating while bad rows remain fails with the same "violated by some row" error, and the constraint stays NOT VALID.

The point of the split is the lock. Checking pg_locks inside each transaction on PostgreSQL 18:

Step	Lock on the table
`ADD CONSTRAINT ... NOT VALID`	`AccessExclusiveLock`, held briefly because there is no scan
`VALIDATE CONSTRAINT`	`ShareUpdateExclusiveLock`, which allows reads and writes during the scan

Foreign keys work the same way. ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID succeeds even with orphaned rows, and VALIDATE CONSTRAINT reports the first one it finds:

ERROR:  insert or update on table "ledger" violates foreign key constraint "ledger_account_fk"
DETAIL:  Key (account_id)=(42) is not present in table "accounts".

PostgreSQL 18 also accepts NOT VALID on a NOT NULL constraint:

SQL

ALTER TABLE users2 ADD CONSTRAINT users2_email_nn NOT NULL email NOT VALID;
ALTER TABLE users2 VALIDATE CONSTRAINT users2_email_nn;

ERROR:  column "email" of relation "users2" contains null values

On earlier versions, the workaround is a CHECK (email IS NOT NULL) NOT VALID constraint, validated and then followed by SET NOT NULL, which PostgreSQL 12 and later can complete without a second scan.

Adding UNIQUE without blocking writes

NOT VALID does not apply to unique constraints. Build the index concurrently, then attach it:

SQL

CREATE UNIQUE INDEX CONCURRENTLY subs_email_idx ON subs (email);
ALTER TABLE subs ADD CONSTRAINT subs_email_key UNIQUE USING INDEX subs_email_idx;

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "subs_email_idx" to "subs_email_key"

Deferrable constraints

By default, UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUDE constraints are checked immediately. That can reject a change that would be valid once complete. Swapping two positions is the standard example:

SQL

CREATE TABLE playlist (
  id int PRIMARY KEY,
  pos int,
  CONSTRAINT playlist_pos_key UNIQUE (pos) DEFERRABLE INITIALLY IMMEDIATE
);
INSERT INTO playlist VALUES (1, 1), (2, 2);

UPDATE playlist SET pos = 2 WHERE id = 1;

ERROR:  duplicate key value violates unique constraint "playlist_pos_key"
DETAIL:  Key (pos)=(2) already exists.

Because the constraint is DEFERRABLE, a transaction can postpone the check until COMMIT:

SQL

BEGIN;
SET CONSTRAINTS playlist_pos_key DEFERRED;
UPDATE playlist SET pos = 2 WHERE id = 1;
UPDATE playlist SET pos = 1 WHERE id = 2;
COMMIT;

 id | pos
----+-----
  1 |   2
  2 |   1

If the data is still invalid at commit, the COMMIT fails and the transaction rolls back.

Even a single statement can trip a non-deferrable unique constraint. On a plain UNIQUE (pos) column with rows at positions 1, 2, and 3:

SQL

UPDATE seats SET pos = pos + 1;

ERROR:  duplicate key value violates unique constraint "seats_pos_key"
DETAIL:  Key (pos)=(2) already exists.

Row 1 moved to position 2 before row 2 had moved out of the way. A deferrable constraint accepts this statement.

SET CONSTRAINTS only works on constraints declared DEFERRABLE:

ERROR:  constraint "seats_pos_key" is not deferrable

Circular foreign keys

INITIALLY DEFERRED makes deferral the default, which solves tables that reference each other. A team needs a captain, and a player needs a team:

SQL

CREATE TABLE teams (id int PRIMARY KEY, captain_id int NOT NULL);
CREATE TABLE players (
  id int PRIMARY KEY,
  team_id int NOT NULL REFERENCES teams (id) DEFERRABLE INITIALLY DEFERRED
);
ALTER TABLE teams ADD CONSTRAINT teams_captain_fk
  FOREIGN KEY (captain_id) REFERENCES players (id) DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO teams VALUES (1, 7);
INSERT INTO players VALUES (7, 1);
COMMIT;

Both inserts succeed and the commit passes. A team whose captain never appears fails at COMMIT:

ERROR:  insert or update on table "teams" violates foreign key constraint "teams_captain_fk"
DETAIL:  Key (captain_id)=(8) is not present in table "players".

NOT NULL and CHECK constraints cannot be deferred. Writing CHECK (n > 0) DEFERRABLE fails with misplaced DEFERRABLE clause.

A deferrable unique constraint, even one that is INITIALLY IMMEDIATE, also cannot be the target of an upsert:

ERROR:  ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters

Make a constraint deferrable only when you need it.

Listing a table's constraints

\d table_name in psql shows them grouped by type. For a query you can run from any client:

SQL

SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'items'::regclass
ORDER BY conname;

      conname       | contype |     definition
--------------------+---------+--------------------
 items_id_not_null  | n       | NOT NULL id
 items_pkey         | p       | PRIMARY KEY (id)
 items_qty_check    | c       | CHECK ((qty >= 0))
 items_qty_not_null | n       | NOT NULL qty
 items_sku_key      | u       | UNIQUE (sku)

contype is c for check, f for foreign key, n for not-null, p for primary key, u for unique, and x for exclusion. The n rows appear on PostgreSQL 18 and later.

Common errors

Error	Cause and fix
`null value in column "x" ... violates not-null constraint`	A required column was missing. Supply a value or add a `DEFAULT`.
`violates check constraint "name"`	The row fails the expression. The DETAIL line shows the failing row.
`duplicate key value violates unique constraint`	The value exists. Use `ON CONFLICT` if an upsert is what you meant.
`is not present in table "x"`	Foreign key target missing. Insert the parent first, or defer the constraint.
`is still referenced from table "x"`	Delete the children first, or set an `ON DELETE` action.
`there is no unique constraint matching given keys`	The referenced column needs a primary key or unique constraint.
`is violated by some row`	Existing data breaks the new constraint. Add it `NOT VALID`, clean up, then validate.
`data type integer has no default operator class for access method "gist"`	Run `CREATE EXTENSION btree_gist;` before the exclusion constraint.
`constraint "x" is not deferrable`	Recreate it with `DEFERRABLE`.

Quick reference

Task	Syntax
Required column	`email text NOT NULL`
Rule on a value	`CHECK (price > 0)`
Rule across columns	`CONSTRAINT n CHECK (discount_price < price)`
Unique value	`email text UNIQUE`
Unique including nulls	`UNIQUE NULLS NOT DISTINCT` (PG15+)
Composite key	`PRIMARY KEY (order_id, line_no)`
Foreign key	`customer_id bigint REFERENCES customers (id)`
Cascade deletes	`REFERENCES posts (id) ON DELETE CASCADE`
No overlapping ranges	`EXCLUDE USING gist (room_id WITH =, during WITH &&)`
Temporal key	`PRIMARY KEY (room_id, valid_during WITHOUT OVERLAPS)` (PG18+)
Add without a long lock	`ADD CONSTRAINT ... NOT VALID;` then `VALIDATE CONSTRAINT n;`
Check at commit	`DEFERRABLE INITIALLY DEFERRED`