UNIQUE, CHECK and Foreign Key Actions

Application code checks data, and application code has bugs, more than one version running, and scripts that skip it. The database is the one place every write passes through. A rule written as a constraint is checked for every writer, including the one-off script someone runs at night. Beginner SQL covered NOT NULL, PRIMARY KEY and simple UNIQUE. This lesson covers the rules that involve more than one column, and what a foreign key does when the parent row goes away.

UNIQUE over more than one column

A promotion code is unique within a region, not across the world. A UNIQUE (code, region) rule makes the pair unique. Name your constraints with CONSTRAINT name, so the error message tells you which rule fired.

CREATE TABLE promos (
  promo_id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code             text NOT NULL,
  region           text,
  discount_percent integer,
  starts_on        date NOT NULL,
  ends_on          date NOT NULL,
  CONSTRAINT promo_code_per_region UNIQUE (code, region),
  CONSTRAINT discount_in_range CHECK (discount_percent BETWEEN 1 AND 90),
  CONSTRAINT valid_dates CHECK (ends_on >= starts_on)
);
INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('SAVE10', 'PH', 10, '2026-10-01', '2026-10-31');

CREATE TABLE
INSERT 0 1

The same code in another region is fine. The same code in the same region is not.

INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('SAVE10', 'SG', 10, '2026-10-01', '2026-10-31');
INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('SAVE10', 'PH', 15, '2026-11-01', '2026-11-30');

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "promo_code_per_region"
DETAIL:  Key (code, region)=(SAVE10, PH) already exists.

The first row went in. The second was refused, and the DETAIL line shows the values that clashed.

UNIQUE and NULL

Here is the surprise. A promotion with no region, meaning "everywhere", has a NULL region. Two NULLs are not equal to each other, so the rule does not see a duplicate.

INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('WELCOME', NULL, 5, '2026-10-01', '2026-12-31'), ('WELCOME', NULL, 5, '2026-10-01', '2026-12-31');
SELECT code, region FROM promos WHERE code = 'WELCOME';

INSERT 0 2
  code   | region
---------+--------
 WELCOME |
 WELCOME |
(2 rows)

Two identical "everywhere" rows are accepted. In PostgreSQL 15 and later you can say UNIQUE NULLS NOT DISTINCT, which treats NULLs as equal for this rule. That keyword is PostgreSQL-specific, and other databases have their own behaviour for NULL in unique rules, so test yours.

CREATE TABLE promos_strict (
  code   text NOT NULL,
  region text,
  UNIQUE NULLS NOT DISTINCT (code, region)
);
INSERT INTO promos_strict VALUES ('WELCOME', NULL);
INSERT INTO promos_strict VALUES ('WELCOME', NULL);

CREATE TABLE
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "promos_strict_code_region_key"
DETAIL:  Key (code, region)=(WELCOME, null) already exists.

CHECK across columns, and the NULL that passes

A CHECK rule can compare columns of the same row. The valid_dates rule above refuses a promotion that ends before it starts.

INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('BACKWARDS', 'PH', 10, '2026-12-01', '2026-11-01');
INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('TOOMUCH', 'PH', 95, '2026-10-01', '2026-10-31');

ERROR:  new row for relation "promos" violates check constraint "valid_dates"
DETAIL:  Failing row contains (6, BACKWARDS, PH, 10, 2026-12-01, 2026-11-01).
ERROR:  new row for relation "promos" violates check constraint "discount_in_range"
DETAIL:  Failing row contains (7, TOOMUCH, PH, 95, 2026-10-01, 2026-10-31).

Both messages name the rule, because we named it. Now the catch. A CHECK passes when its expression is true or NULL. Only false fails.

INSERT INTO promos (code, region, discount_percent, starts_on, ends_on)
VALUES ('NOPERCENT', 'PH', NULL, '2026-10-01', '2026-10-31');

INSERT 0 1

The discount is missing and the row is accepted, because NULL BETWEEN 1 AND 90 is unknown, not false. If the column must have a value, add NOT NULL as well. A CHECK does not replace it.

Foreign key actions: what happens to the children

A foreign key says a child row needs a parent. The other half of the rule is what to do when someone deletes the parent. The default, NO ACTION, refuses. Try it on the shop: delete an order that has items.

DELETE FROM orders WHERE order_id = 1;

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

The message names the rule and the child table. You choose the behaviour when you create the key: ON DELETE RESTRICT (refuse, and do not wait for the end of the transaction), CASCADE (delete the children too), SET NULL (keep the child and clear the link) or SET DEFAULT. Each fits a different relationship. A wishlist belongs to the customer and means nothing without them. A support ticket is history and should outlive the account.

CREATE TABLE wishlist_items (
  customer_id integer NOT NULL REFERENCES customers (customer_id) ON DELETE CASCADE,
  product_id  integer NOT NULL REFERENCES products (product_id),
  PRIMARY KEY (customer_id, product_id)
);
CREATE TABLE support_tickets (
  ticket_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer REFERENCES customers (customer_id) ON DELETE SET NULL,
  subject     text NOT NULL
);
INSERT INTO wishlist_items VALUES (4, 1), (4, 3), (1, 2);
INSERT INTO support_tickets (customer_id, subject) VALUES (4, 'Wrong colour'), (1, 'Late delivery');
DELETE FROM customers WHERE customer_id = 4;
SELECT * FROM wishlist_items ORDER BY customer_id, product_id;
SELECT * FROM support_tickets ORDER BY ticket_id;

CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 2
DELETE 1
 customer_id | product_id
-------------+------------
           1 |          2
(1 row)

 ticket_id | customer_id |    subject
-----------+-------------+---------------
         1 |             | Wrong colour
         2 |           1 | Late delivery
(2 rows)

Dan Lim's two wishlist rows went with him. His support ticket stayed, with no customer. The wishlist of customer 1 is untouched. Ana still cannot be deleted, because her orders use the default rule.

DELETE FROM customers WHERE customer_id = 1;

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

PostgreSQL does not create an index on the child's referencing column for you. A cascade or a join on that column will scan the child table until you add one. The last section of this course looks at indexes.

Common mistakes

• Leaving constraints unnamed, so the error says promos_check1.
• Expecting UNIQUE (a, b) to stop duplicate rows when b is NULL.
• Writing a CHECK and forgetting NOT NULL.
• Using CASCADE on a table that holds history.
• Relying on the application to enforce a rule that the database could.

If you skip this, bad rows will arrive from a script nobody remembers, and they will sit in your reports until someone notices the totals. Which rule in your schema exists only in the application code?