Re: on cascade set null works on not null columns

Baldur Norddahl wrote:
> Hi,
>
> I just noticed that I could do this:
>
> webshop=# create table foo (bar text not null primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for
> table "foo"
> CREATE TABLE
> webshop=# create table foo2 (bar text not null, foreign key (bar) references
> foo(bar) on update cascade on delete set null);
> CREATE TABLE
> webshop=# insert into foo values ('a');
> INSERT 6644065 1
> webshop=# insert into foo2 values ('a');
> INSERT 6644066 1
> webshop=# delete from foo;
> ERROR: null value in column "bar" violates not-null constraint
>
> I would have expected the second create table to fail. This didn't allow me to
> violate constraints, but it made the error message unintuitive. You get no
> clues to which table is actually preventing me from deleting from 'foo'. This
> is in contrast to if I use no action:
>
> webshop=# drop table foo2;
> DROP TABLE
> webshop=# create table foo2 (bar text not null, foreign key (bar) references
> foo(bar) on update cascade on delete no action);
> CREATE TABLE
> webshop=# insert into foo2 values ('a');
> INSERT 6644189 1
> webshop=# delete from foo;
> ERROR: update or delete on "foo" violates foreign key constraint "$1" on
> "foo2"
> DETAIL: Key (bar)=(a) is still referenced from table "foo2".
>
> This time I get a useful error message.

We have a TODO item to print the table name with the constraint name:

* Print table names with constraint names in error messages, or
make constraint names unique within a schema

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073