[
https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569200#action_12569200
]
Kristian Waagan commented on DERBY-3299:
----------------------------------------
I took 'd3299_caUtilMethods_v1.patch' out for a spin. I haven't reviewed the
code, but noticed that the JavaDoc for
DDLSingleTableConstantAction.executeConglomReplacement uses an incorrect
parameter name.
I don't have any comments on the approach and code otherwise, I was just a
little curious and had a quick look :)
> 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.
