Re: SET NULL / SET NOT NULL

On Tue, 26 Mar 2002, Christopher Kings-Lynne wrote:

> > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > > I have throught of at least two problems with changing nullability.  The
> > > first is primary keys.  I have to prevent people setting a
> > column involved
> > > in a PK to null, right?
> >
> > Probably so.
>
> And foreign keys - foreign keys only have to reference UNIQUE, right?  The
> nullability isn't an issue?

That should be fine.

> Lastly - in a multicolumn primary key, does EVERY column in the key need to
> be NOT NULL?

Well, it looks like the primary key will not be satisfied if any of the
values are NULL.

In my SQL 92 draft, 11.7 Syntax Rules 3a says:   If the <unique specification> specifies PRIMARY KEY, then let   SC be
the<search condition>:
 
                UNIQUE ( SELECT UCL FROM TN )                AND                ( UCL ) IS NOT NULL