Re: how could a foreign key ever be NULL?

On Mon, 17 Dec 2001, Terrence Brannon wrote:

> The Momjian book states:
>
> A single-column foreign key is either NULL or matches a primary key
>
> But the problem I have with this statement is that a primary key
> cannot be null or duplicate. And since the foreign key references
> a value that is neither null or duplicate, how could it ever be
> NULL?

First, you don't need to reference a primary key.  Any unique constraint
should work (11.8 referential constraint definition, Syntax rules 2a),
"If the <referenced table and columns> specifies a <reference column
list>, then the set of column names of that <reference column list>
shall be equal to the set of column names in the unique columns of
a unique constraint of the referenced table."  You're probably confusing
it with the behavior that if no column list is given it goes to
the primary key columns (11.8 SR2b I believe).

As for the NULL part, 4.10.2 Table Constraints, (ignoring the not
implemented match partial), "A referential constraint is satisfied
if one of the following conditions is true, depending on the <match
option> specified in the <referntial constraint definition>:- If no <match type> was specified then, for each row R1 of
the referencing table, either at least one of the values of the  referencing columns in R1 shall be a null value, or
thevalue of  each referencing column in R1 shall be equal to the value of the  corresponding referenced column in some
rowof the referenced  table.-  If MATCH FULL was specified then, for each row R1 of the refer-   encing table, either
thevalue of every referencing column in R1   shall be a null value, or the value of every referencing column   in R1
shallnot be null and there shall be some row R2 of the   referenced table such that the value of each referencing col-
umn in R1 is equal to the value of the corresponding referenced   column in R2."