[ http://issues.apache.org/jira/browse/DERBY-338?page=all ]
Rick Hillegas updated DERBY-338:
--------------------------------
Urgency: Normal
> 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
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.0.2.1, 10.0.2.0, 10.1.1.0, 10.0.2.2
> Reporter: Andrew McIntyre
> Assigned To: Jack Klebanoff
> Fix For: 10.2.0.0
>
> Attachments: refConstraint2005-06-07.diff,
> refConstraint2005-06-08.diff
>
>
> 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
