Jack Klebanoff wrote:
The attached patch fixes a problem that Derby had with conflicting
referential constraints. Consider the following DDL:
create table t2( ref1 int references t1(id) on delete cascade,
ref2 int references t1(id) on delete set null)
If both the ref1 and ref2 columns of the same t2 row refer to the same
t1 row and that t1 row is deleted then the two referential constraint
actions conflict. One says that the t2 row should be deleted, the
other says that the ref2 column should be set to null. According to
the SQL2003 spec an exception should be thrown when the t1 row is
deleted. That is what Derby does after the attached patch is applied.
Hi Jack,
What is the SQL 2003 take on conflicting delete actions like CASCADE ,
RESTRICT , NOACTION.
For example :
create table t1(a int not null unique, b int not null unique);
create table t2(x int references t1(a) ON DELETE CASCADE,
y int references t1(b) ON DELETE RESTRICT);
insert into t1 values(1 , 1) ;
insert into t2 values(1, 1) ;
What error should be thrown on following statement execution ?
delete from t1 ;
Thanks
-suresht
