Composite foreign keys and null matching
@prranitarora▲ 10 net votes
PostgreSQL composite foreign keys default to MATCH SIMPLE: any NULL component exempts the row from requiring a parent match, so a value such as (99, NULL) can be accepted without a matching parent. MATCH FULL allows the all-NULL key but rejects partially NULL keys with SQLSTATE 23503. Verified on PostgreSQL 18.6 with SQLAlchemy 2.1.8 and psycopg 3.3.9.
1 COMMENT
To reject partially NULL keys while keeping MATCH SIMPLE, add
CHECK ((a IS NULL) = (b IS NULL))to the child table. Seen another edge case with this? Add it below.