Duplicate Key Error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Duplicate Key Error

I am trying to insert 3500 rows from table budget04 to budget05. I have an IDENTITY field in budget05 where there is no chance of inserting duplicate key but still I get this error message Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint ‘IX_budget05. Cannot insert duplicate key in object budget05.
The statement has been terminated.
Thanks!
"He laughs best who laughs last"
On what column(s) is your constraint defined on? Even though an IDENTITY column will ensure uniqueness, a unique constraints may be present in other columns. Are you inserting via a file or straight insert scripts? Try inserting your data into a copy of the table that does not have the unique constraint. You can then check to see what row has the data that will violate the unique contraint by comparing the data and checking for duplicates in a particular column. – Tahsin
Post your table structure and make sure there are no duplicates value for the column budget05 Madhivanan Failing to plan is Planning to fail
If you primary key includes only IDENTITY column its named as PK_column_name Looks like you have one more unique constraint. Taka a look on definition of "IX_budget05" – I guess it includes some column except primary key.
I also suppose you are not trying to insert values from IDENTITY columns from Budget04 (saying SET INEDTITY ON or something else). Maksim
Also remind yourself that there is no point in having a UNIQUE constraint on multiple columns if one of those columns is an IDENTITY column.
I have unique constraint on 3 other columns, I tried deleting the constraint IX_budget05 and insert the table but I coudnt insert complete data some data in some columns is missing. why is it so?
How can I insert full data without missing anything. Thanks!
"He laughs best who laughs last"
Any foreign key constraints on any of the columns?
yeah I do have 7 FK constraints. Thanks!
"He laughs best who laughs last"
So you have to make sure you’re inserting values on those FK columns that have a match in the ‘lookup’ RK table. This is standard procedure when inserting foreign data into local tables.
I dont have any data in the FK tables, this is a complete empty DB, I am trying to insert data into a table from different DB, this will be my 1st table with data. Thanks!
"He laughs best who laughs last"
The same rule applies: you can only insert rows where the FK columns have data, and there are already corresponding values in the RK ‘lookup’ table.
quote:Originally posted by Reddy I have unique constraint on 3 other columns, I tried deleting the constraint IX_budget05 and insert the table but I coudnt insert complete data some data in some columns is missing. why is it so?
How can I insert full data without missing anything.

What do you meant saying "some data is missing", why its missing in some columns? Do you mean when inserting a row, only part of row is inserted and other part is missing? You see what causing the error – SQL server response which constraint is violated. If you are getting an error of IX_budget05, you must to make sure everything required by this constraing is observed. The same rule for foreign keys – when its violated you are getting an error about it. If you are sure you don’t need constraint IX_budget05 anymore and data will stay consistant if you delete it and insert data – do it! But remember that generally constraint doing his job and defined for something… Other approach – maybe you really have problem with you data??? Maybe solution is to exclude duplicated keys when inserting data into table and let to constraint stay? Maksim
Hi, TBL1 (Primary Table)
—————–|
Col1|Col2 |
—————–|
1|text1 |
2|text2 |
—————– TBL2
————————————————-
Col1|Col2|Col3 |(Foreign Key from TBL1.Col1)
————————————————-
test1|abc|1|
test2|def|2|
test3|hij||"<- This is not possible, as foreign key
—————————————————————- Here, you will get error while inserting 3rd row as system will validate foreign key relation and it will fail. As suggested, it will be easier for other ppl to help you if you can post your table structure and query which you are trying to execute. Regards,
Waqar.
]]>