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.4.0.0
Reporter: A B
Priority: Minor
Attachments: case_2.sql
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.
