Re: Foreign keys and null

Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de> writes:
> I wasn't aware that a foreign key actually _can_ be null.

> The docs weren't very specific about that. Thinking that
> a foreign key must contain a valid reference to another
> table (after all, that's what a foreign key is for), I
> implied that it cannot be null -- which was wrong.

If you put a NOT NULL constraint on the column along with the FOREIGN
KEY constraint, then every entry must be a valid reference. However,
in cases where you want some of the rows to have no reference, you
leave off the NOT NULL. Simple eh?

Also, if you are using multi-column foreign keys, there is a MATCH
option that determines the semantics of partially-null key sets.
But that might be getting far afield for the novice list...

regards, tom lane