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 [PATCH] Referential Constraints
Date Wed, 01 Jun 2005 14:41:10 GMT
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.

Without the patch Derby disallows the DDL statement that creates a 
constraint whose action may conflict with other constraint actions. This 
is a mistake. Derby cannot tell at DDL time whether there will actually 
be a conflict. Derby rejects the DDL statement in the example above. 
However if none of the t2 rows refer to the same t1 row then there is no 
conflict. Derby rejects some DDL that will work perfectly well. As, I 
said above this is contrary to the SQL standard which requires that the 
checks be made at DML execution time not at DDL execution time.

The patch changes iapi/sql/dictionary/DDUtils.java to remove the DDL 
time checks and changes impl/sql/execute/DeleteCascadeResultSet.java, 
DMLWriteResultSet.java, and UpdateResultSet.java to add the DML 
execution time checks. The execution time checks are implemented by 
building a hash table of all the rows that are changed as the result of 
referential constraint actions. An exception is thrown if any row is 
entered into the hash table twice with different actions. The hash table 
is implemented with BackingStoreHashtable in case it is too large to fit 
into memory.

The lang/refActions2.sql test was changed to reflect the changed Derby 
functionality.

The patch has passed the derbyall test suite.

Svn status is:
M      
java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java
M      java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
M      java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
M      java/engine/org/apache/derby/iapi/sql/dictionary/DDUtils.java
M      java/engine/org/apache/derby/iapi/reference/SQLState.java
M      java/engine/org/apache/derby/loc/messages_en.properties
M      
java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions2.sql
M      
java/testing/org/apache/derbyTesting/functionTests/master/refActions2.out

Jack Klebanoff

Mime
View raw message