db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew McIntyre (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-338) Move checks for referential constraints from DDL execution time to DML execution time
Date Sun, 05 Jun 2005 22:55:39 GMT
Move checks for referential constraints from DDL execution time to DML execution time

         Key: DERBY-338
         URL: http://issues.apache.org/jira/browse/DERBY-338
     Project: Derby
        Type: Bug
  Components: SQL  
    Reporter: Andrew McIntyre
 Assigned to: Jack Klebanoff 
     Fix For:

Description for this bug from Jack Klebanoff:

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

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

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message