[
https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569039#action_12569039
]
A B commented on DERBY-3299:
----------------------------
derbyall and suites.All using ibm142 ran cleanly with
d3299_createIxAction_v1.patch, so I committed that with svn # 627836:
URL: http://svn.apache.org/viewvc?rev=627836&view=rev
derbyall and suites.All also ran cleanly with d3299_caUtilMethods_v1.patch
(which applied cleanly after createIxAction). If tinderbox tests for
createIxAction run cleanly, and if I hear no objections/feedback on
d3299_caUtilMethods_v1.patch, I plan to commit the latter tomorrow (Feb 15 PST).
> 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.0.0
> Reporter: A B
> Assignee: A B
> Priority: Minor
> Attachments: case_2.sql, d3299_caUtilMethods_v1.patch,
> d3299_createIxAction_v1.patch, d3299_dropSharedConglom_v1.patch,
> d3299_tests_v1.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.
-
You can reply to this email to add a comment to the issue online.
