[
https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-3299:
-----------------------
Attachment: d3299_dropSharedConglom_v2.patch
Attaching d3299_dropSharedConglom_v2.patch, which has been updated to account
for the commit of d3299_caUtilMethods_v2.patch. This second version also has
a minor change to the createNewBackingCongloms(...) method of
AlterTableConstantAction--esp. _v2 removes an unnecessary parameter from that
method.
I've started derbyall with this patch applied; if all goes well and I hear no
objects/feedback, I plan to commit it early next week (Mon or Tues 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_caUtilMethods_v2.patch, d3299_createIxAction_v1.patch,
> d3299_dropSharedConglom_v1.patch, d3299_dropSharedConglom_v2.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.
