Suresh Thalamati wrote:
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
According to my copy of the SQL2003 spec "then an exception condition is
raised: triggered data change violation". Thanks for your question. I
realized that I am not using the SQLState specified by SQL2003. I will
make a new patch.
Jack
.
