Re: Foreign Keys

On Fri, 21 Nov 2003, Thomas LeBlanc wrote:

> I am able to enter a null in a foreign key field in one table(table2)
> without the foreign key relationship (table1.t1_id) validating the value.
> Why is this?

To go into more detail from the previous answers:
 For a single column key, a NULL value is always allowed in the
referencing table (table2) and isn't considered to be referencing
any row in table1.
 For a multiple column key, how NULLs are handled depends on the match
type specified.  For the unspecified match type/MATCH SIMPLE, if any field
in the referencing table is NULL, the constraint is considered satisified
and doesn't reference any particular row in table1. For MATCH FULL, either
all the columns must be NULL for a row (and the constraint is satisified
and there is no referenced row) or none of the columns may be NULL and
the check is done normally.  For MATCH PARTIAL (which we don't implement),
if all columns are NULL the constraint is satisified and there is no
referenced row, if all columns are non-NULL the check is done normally,
if some columns are NULL and others are non-NULL, the non-NULL columns
are checked against the referenced table without concern for matching the
columns where the referencing row has a NULL.