db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-3299) Uniqueness violation error (23505) occurs after dropping a PK constraint if there exists a foreign key on the same columns.
Date Fri, 27 May 2011 22:40:47 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13040485#comment-13040485
] 

Kathey Marsden commented on DERBY-3299:
---------------------------------------

I think it makes sense to try to reduce the test case a bit and add it in as an upgrade test.
But just as a data point, I noticed that the duplicate row is created with 10.1.1.0 but not
the latest on the 10.1.
branch, so likely a backported bug fix.    10.3.1.4 the earliest release of 10.3 didn't show
the problem.





> Uniqueness violation error (23505) occurs after dropping a PK constraint if there exists
a foreign key on the same columns.
> ---------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3299
>                 URL: https://issues.apache.org/jira/browse/DERBY-3299
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.4.1.3
>            Reporter: A B
>            Assignee: A B
>            Priority: Minor
>             Fix For: 10.4.1.3
>
>         Attachments: case_2.sql, d3299_caUtilMethods_v1.patch, d3299_caUtilMethods_v2.patch,
d3299_createIxAction_v1.patch, d3299_dropSharedConglom_v1.patch, d3299_dropSharedConglom_v2.patch,
d3299_tests_v1.patch, d3299_tests_v2.patch, d3299_v1.patch
>
>
> When there are multiple constraints on a single table and the constraints have the same
set of columns (in the same order), Derby tries to optimize things by re-using a single backing
index for all of the relevant constraints.  See the "executeConstantAction()" method of CreateIndexConstantAction.java
(search for "duplicate").
> But there is a bug in Derby where, if one of the constraints is unique and is dropped,
the uniqueness "attribute" of the backing index is not updated accordingly.  This means that
uniqueness may be incorrectly enforced where it is not required.
> Take the following example ("Case 2" from DERBY-2204):
>   ALTER TABLE NEWORDERS ADD CONSTRAINT
>       NEWORDERS_PK PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID);
>   ALTER TABLE NEWORDERS ADD CONSTRAINT
>       NO_O_FK FOREIGN KEY (NO_W_ID, NO_D_ID, NO_O_ID) REFERENCES ORDERS;
> For these statements Derby will use a single backing index for both the primary constraint
NEWORDERS_PK and the foreign key constraint NO_O_FK.  That backing index will be unique because
the primary key must itself be unique.
> If later we drop the primary key:
>   ALTER TABLE NEWORDERS DROP CONSTRAINT NEWORDERS_PK;
> then the backing index needs to be converted from a unique index to a non-unique index
(because a foreign key is not inherently unique).  But in Derby the uniqueness attribute remains
unchanged, so attempts to insert a duplicate (NO_W_ID, NO_D_ID, NO_O_ID) row into NEWORDERS
will fail with error 23505, when it should really succeed.
> I tried this out on 10.1.3.1 and the same behavior occurs there, so marking "Affects"
versions for everything back to that...

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message