CODEWITHSUNDEEP
sqldatabasesql-insertfirebird2.5ibexpert
user3327902
user3327902

Reputation: 31

Firebird2.5 Error: "duplicate value in unique index" without duplicate data

Hello and thanks for reading and probably also helping me

brief explanation of my problem:

I am copying Data from one Firebird-Database to another (called V14) and i am using IBExpert to do so. The tables are named equally and have the same structure. For the purpose of explaining i am going to call the table containing the data in the old database A and the table i want to insert the data into shall be called B.

So the only thing to do is to take all Data from table A and insert them into table B. The small piece of Code to do so is:

INSERT into [V14].BSMZ SELECT * FROM BSMZ

executing this i receive the error-Message (after some rows were transfered):

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. attempt to store duplicate value (visible to active transactions) in unique index "UI_BSMZ"

This unique-Index contains 2 Data-colums and applies to table B and A.

There are multiple rows that cause this problem but also multiple rows are transfered as desired. I have prooved that the rows, that can't be inserted into table B have values in their unique-key-colums, that are not alredy present in table B.

(For purpose of testing i did remove the constraint from table B. But i still get the same error-message, wich confuses me even more)

I am not really sure what could cause this problem and would appreciate some hints.

Upvotes: 3

Views: 6993

Answers (2)

Yurii
Yurii

Reputation: 301

I had a similar problem: after copying data I couldn't insert new row into the table. Turned out it was related to ID generator. So I simply found generator in database, and increased value of ID to bigger, then maximum ID value in the table.

Upvotes: 0

Livius
Livius

Reputation: 956

you got exactly error info unique index "UI_BSMZ" find this declaration of unique constraint - it is not about foreign key from B to A this is constraint in table on some field[s]

in source database run query on fields from this UK UI_BSMZ

SELECT UK_FIELD_1, UK_FIELD_2, .. FROM TABLE_NAME GROUP BY UK_FIELD_1, UK_FIELD_2, .. HAVING     COUNT(*)>1 PLAN (TABLE_NAME NATURAL)

if this query return some rows - than in source database index is corrupted - or not exists

to fix index (after deleting duplicates) - run this:

Alter Index INDEX_NAME ACTIVE;

this rebuild your index

Upvotes: 1

Related Questions