8

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.

2
  • It's not "because" of the reasons you give. It's because that's what the designers of SQL decided, because of a general vague motivation of associating NULL with a normal value of a type being unavailable or inapplicable. But the details are not consequences of any particular well-founded or unified theory or design. We must memorize how features act--although we can sometimes organize them in terms of simpler ideas. Example: A FK subrow with a NULL cannot match or be a duplicate of or SQL-equal a PK subrow. So the FK's constraint "should" fail using "normal" NULL reasoning. But it doesn't.
    – philipxy
    Apr 21 '20 at 3:36
  • "there is no link" is not clear. Use technical terms, correctly. (It is best to never use "link".) You might be trying to say something about a FK subrow not appearing as a referenced PK subrow; but you might be trying to say something about a FK constraint failing or succeeding; or you might be trying to say something about both.
    – philipxy
    Apr 21 '20 at 3:50
7

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.

3
  • The fiddle is so interesting, I did not know something like that existed. Very easy to try out stuff! I am going to experiment some stuff and come back here!
    – Gnarfly
    Apr 19 '20 at 15:49
  • Yes, there are also a couple of more alternatives out there. Very cool to able to try things in DBMS that you don't have installed locally.
    – Lennart
    Apr 19 '20 at 15:51
  • Just great and very useful!
    – Gnarfly
    Apr 19 '20 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.