February 19th, 2014, 03:36 AM
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.
The small piece of Code to do so is:
unfortunately i receive the error-Message:
INSERT into [V14].BSMZ SELECT * FROM BSMZ
The unique-Index contains 2 Data-colums.
In the table i take my data from, a unique index is also present and identically defined.
There are multiple rows that cause this problem.
But each row really has unique values in the 2 colums of the unique key.
I am not really sure what could cause this problem and would appreciate some hints.
(For purpose of testing i did remove the constraint from the table i want to put my data in. But i still get the same error-message, wich confuses me even more)
February 19th, 2014, 08:36 AM
Do you make another inserts in this table from somewhere else in your code? Do you commit/rollback transaction after each execution of the code? Because IBExpert by default doesn't commit and if you run your code 2-3 times in the same transaction is normal to trigger the unique constraint.
Also try to make a twin table in the same database and copy the data in it to see if the error occur.
Last edited by mIRCata; February 20th, 2014 at 01:05 AM.
February 19th, 2014, 09:10 AM
There is no other insert. The line of code shown in my post is every source code that is executed. And i empty the table before running this code.
creating a twin-table could be worth a try..
February 20th, 2014, 01:07 AM
Is there a trigger in the table, active before insert that changes the data by some way?
February 20th, 2014, 04:14 AM
There is a trigger - but it does not change the data of the unique-key-colums.
Originally Posted by mIRCata
Another thing to check is null or empty values. Null will register as a value in the index.
Originally Posted by Marko84
If the trigger is doing an insert into a second table that may be causing it.
Another option (being paranoid) is use a distinct on the select query and lastly have you checked a count on the rows having the count value > 1? The data may be meant to be unique but it's useful to check.