Thread: on cascade set null works on not null columns

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.

Baldur



----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

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