3

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.

user3327902
  • 31
  • 1
  • 2
  • In some cases it might occur with a corrupt index that backs the unique constraint. Also check that there are no null values in one of the constrained columns. – Mark Rotteveel Feb 19 '14 at 20:23
  • There are no null-Values in the constrained colums. Maybe i should ask google how to fix a corrupt index and take a look if this solves the issue... – user3327902 Feb 20 '14 at 06:50
  • Another thing: does the constraint include (var)char columns? If so, could it be a column with a case insensitive and/or accent insensitive collation? – Mark Rotteveel Feb 20 '14 at 08:37
  • It contains an integer and a VarChar(6) colum. But the VarChar is only used to store numbers. (dont ask me why, i did not design it - i just have to cope with it ^^) – user3327902 Feb 20 '14 at 10:19

2 Answers2

1

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

Livius
  • 958
  • 1
  • 6
  • 19
0

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.

Yurii
  • 171
  • 2
  • 17