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...

Chris


Tue, 28 Jan 2003 12:15:40 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
SET NULL on NOT NULL field
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I just noticed you can do this:
> create table blah (
> 	a not null references test on delete set null
> )

> Should that be prevented?

It already does.  Or did you mean disallow the declaration?  I can't see
anything in SQL92 that recommends disallowing the declaration.

			regards, tom lane

Mon, 27 Jan 2003 23:49:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET NULL on NOT NULL field 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > I just noticed you can do this:
> > create table blah (
> > 	a not null references test on delete set null
> > )
>
> > Should that be prevented?
>
> It already does.  Or did you mean disallow the declaration?  I can't see
> anything in SQL92 that recommends disallowing the declaration.

Hmmm, well you do get the 'failed to update null value in not null field'
when you actually delete something from the foreign table, but I guess
there's no reason to actually ban the declaration, as silly as it is...

Chris


Tue, 28 Jan 2003 13:03:38 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: SET NULL on NOT NULL field 

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.


Mon, 27 Jan 2003 21:23:01 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: SET NULL on NOT NULL field
On Mon, Jan 27, 2003 at 21:23:01 -0800,
  Stephan Szabo <sszabo@megazone23.bigpanda.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

Tue, 28 Jan 2003 10:45:29 -0600
Bruno Wolff III <bruno@wolff.to>
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@megazone23.bigpanda.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.


Tue, 28 Jan 2003 09:03:28 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: SET NULL on NOT NULL field