Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0
-
None
-
Low
Description
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 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.