db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: [PATCH] Referential Constraints
Date Wed, 08 Jun 2005 22:12:48 GMT
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.



View raw message