By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

on cascade set null works on not null columns

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.