db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Assigned: (DERBY-338) Move checks for referential constraints from DDL execution time to DML execution time
Date Wed, 02 Jun 2010 07:25:41 GMT

     [ https://issues.apache.org/jira/browse/DERBY-338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Kristian Waagan reassigned DERBY-338:

    Assignee:     (was: Kristian Waagan)

Assigned and unassigned to clear in-progress state.

> Move checks for referential constraints from DDL execution time to DML execution time
> -------------------------------------------------------------------------------------
>                 Key: DERBY-338
>                 URL: https://issues.apache.org/jira/browse/DERBY-338
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:,,,
>            Reporter: Andrew McIntyre
>         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
> 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.
You can reply to this email to add a comment to the issue online.

View raw message