I am wondering why Postgres allows to insert a non-existing values for a foreign key to a nullable multicolumn unique constraint. (Cannot be an actual primary key.)
I made a fiddle. I expected this to fail:
INSERT INTO c (intermediate_item_id, intermediate_extra_item_id) VALUES (5,null);
I don't understand why it is possible. Doesn't it break the concept of relational databases?
With default MATCH SIMPLE behavior, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null. The (non-default) MATCH FULL behavior enforces your desired behavior. See:
Also, your whole setup can be radically simplified with the NULLS NOT DISTINCT clause for the UNIQUE constraint since Postgres 15. See:
So:
CREATE TABLE p (
item_id int NOT NULL
, extra_item_id int
, UNIQUE NULLS NOT DISTINCT (item_id, extra_item_id) -- !
);
CREATE TABLE c (
intermediate_item_id int
, intermediate_extra_item_id int
, FOREIGN KEY (intermediate_item_id, intermediate_extra_item_id)
REFERENCES p (item_id, extra_item_id) MATCH FULL -- !
);
Wouldn't work for an actual PRIMARY KEY (instead of the UNIQUE constraint) like you formulated a bit loosely at first, since PK columns are NOT NULL implicitly. See:
MATCH SIMPLEis the default, which allows this. You wantMATCH FULL. See eg dbfiddle.uk/vsN_MLZbMATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)(version 18, currently in Beta)