Types, Constraints & Database Objects

Foreign Keys and Referential Actions

A foreign key requires each non-null referencing value to match a referenced candidate key; update/delete actions define lifecycle consequences, while action timing and constraint deferral are distinct and vendor-dependent.

A foreign key targets a primary or alternate candidate key.
CASCADE propagates identity or lifecycle changes.
SET NULL preserves the child by removing the relationship.
NO ACTION and RESTRICT are not universally synonymous.
Deferral controls when a constraint is checked.
Composite nullable foreign keys need a match policy.

CREATE TABLE shipment_items (
  shipment_id UUID NOT NULL REFERENCES shipments(shipment_id) ON DELETE CASCADE,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  PRIMARY KEY (shipment_id, order_id, line_no),
  CONSTRAINT fk_shipment_item_order_line FOREIGN KEY (order_id, line_no)
    REFERENCES order_items(order_id, line_no) ON DELETE NO ACTION
    DEFERRABLE INITIALLY IMMEDIATE
);

BEGIN;
SET CONSTRAINTS fk_shipment_item_order_line DEFERRED;
UPDATE order_items SET line_no = line_no + 1000 WHERE order_id = $1;
-- References are temporarily inconsistent until the child keys follow.
UPDATE shipment_items SET line_no = line_no + 1000 WHERE order_id = $1;
COMMIT;

-- Use ON DELETE RESTRICT on a separate FK when the parent change must
-- be rejected immediately; PostgreSQL does not defer that action.

Parent lifecycle actions
Action	When parent is deleted	Suitable example
`CASCADE`	Delete matching children	Order owns order items
`RESTRICT`	Refuse referenced change at action time	Product with retained dependent rows
`NO ACTION`	Refuse if violation exists at check time	Default protective relationship; may be deferred where supported
`SET NULL`	Null the child reference	Optional referrer/product link
`SET DEFAULT`	Write declared default, which must still satisfy FK	Rare sentinel policy

Immediate versus deferred answers “when is consistency tested?”; the action answers “what transformation follows a parent change?” A deferred NO ACTION constraint can allow a transaction to reorder keys temporarily, but it must be valid at its deferred check or commit.