#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    3
    Rep Power
    0

    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:
    Code:
    INSERT into [V14].BSMZ SELECT * FROM BSMZ
    unfortunately i receive the error-Message:
    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"
    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)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    3
    Rep Power
    0
    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..
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Is there a trigger in the table, active before insert that changes the data by some way?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    3
    Rep Power
    0
    Originally Posted by mIRCata
    Is there a trigger in the table, active before insert that changes the data by some way?
    There is a trigger - but it does not change the data of the unique-key-colums.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0
    Originally Posted by Marko84
    There is a trigger - but it does not change the data of the unique-key-colums.
    Another thing to check is null or empty values. Null will register as a value in the index.

    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.

IMN logo majestic logo threadwatch logo seochat tools logo