Postgresql – NULL in foreign key column(s)

foreign keypostgresql

I have been learning about SIMPLE and FULL matching foreign keys in PostgreSQL and I am wondering if the following thought process is correct:

Once at least one of the referencing columns of the foreign key contains a NULL value, there is no link to the referenced table. This is because a NULL is not comparable with another NULL value, as per 3VL. This also means that defined cascading actions (such as ... DO DELETE, ... DO SET NULL, ..) when a referenced row which contains at least one NULL value in the relevant columns, is updated or deleted will never happen, because there is no link to the referenced row. More concrete, assume a foreign key from table A(x, y) to B(x, y). If both A and B contain a row (5, NULL) for x and y, there is no link, because the NULL for the row in A is not equal to the NULL for the row in B.

Is this correct? I am quite new to SQL, so I am wondering if I understood this correctly.

Best Answer

You are quite right. I have created a small example at Fiddle

create table p
( a int not null
, b int
, unique (a,b)
);

create table c
( a int not null
, b int
, constraint fk_p foreign key (a,b)
                  references p (a,b)
                      on delete cascade
                      on update cascade
);

-- insert data in parent:
insert into p (a,b) values (1,null);  -- ok
insert into p (a,b) values (1,null);  -- also ok. The constraint evaluates to null,
                                      -- which is ok. The rule is that it must not 
                                      -- evaluate to false

-- insert data in child
insert into c (a,b) values (1,null);  -- ok
insert into c (a,b) values (2,null);  -- also ok, foreign key evaluates to null. 
                                      -- So despite there is no 2 in
                                      -- parent this is ok

-- remove data from parent
delete from p;

-- all rows present in child
select * from c;

a   b
1   null
2   null

Null makes even trivial things a bit tricky to grasp. I would not recommend candidate keys that include nulls.

Related Question