[ https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569311#action_12569311 ]
A B commented on DERBY-3299: ---------------------------- > I don't have any comments on the approach and code otherwise, I was just a > little curious and had a quick look Thanks Kristian, every little bit helps :) I'll make the javadoc fix before committing... > 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.