Re: SET NULL on NOT NULL field

On Tue, 28 Jan 2003, Bruno Wolff III wrote:

> On Mon, Jan 27, 2003 at 21:23:01 -0800,
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
> >
> > On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote:
> >
> > > I just noticed you can do this:
> > >
> > > create table blah (
> > > a not null references test on delete set null
> > > )
> > >
> > > Should that be prevented? It shouldn't be too hard to test for really...
> >
> > Maybe, although I don't think the spec prevents it. In practice
> > I'd guess it ends up being a more expensive way of saying no action.
>
> No. You end up not being able to delete the referenced keys. I tested
> this in 7.3 and you get the following message when you try it:
> ERROR: ExecUpdate: Fail to add null value in not null attribute col1

Right, and NO ACTION shouldn't allow you delete the referenced keys either
except that it gives you a meaningful error message as well. :) I think
you may have been confusing NO ACTION and CASCADE.