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.
