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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073