asked
Unusual Unique Constraint Issue
I have a table with three columns. First being the PK, second being a FK to another table and the third being a column with a Unique Constraint, nonclustered. When I inserted into this table, my insert fails with this error: > Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:24:53 AM Error: 2011-05-19 11:54:43.09 Code: 0x00000000 Source: Insert Data Description: Violation of UNIQUE KEY constraint 'uk_Piece'. Cannot insert duplicate key in object 'dbo.Piece'. End Error Error: 2011-05-19 11:54:43.12 Code: 0xC002F210 Source: Load Data Execute SQL Task Description: Executing the query "EXEC spImportInvoice" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:24:53 AM Finished: 11:54:43 AM Elapsed: 1789.34 seconds. The package execution failed. The step failed.* Obviously, the step fails due to an insert. I open the sproc and run the insert by hand and ofcourse it fails with this error: Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'uk_Piece'. Cannot insert duplicate key in object 'dbo.Piece'. The statement has been terminated. I have done a group by on the piece column, i have selected the data and then used a subquery with an IN clause to make sure no dupes. However, it is still failing. Has anybody experienced this? Is this some kind of anomaly? Am I going crazy? My code is below: INSERT INTO dbo.Piece (Product_Key, Piece_ID) SELECT DISTINCT p.Product_Key, import.Piece_ID FROM tblInvoiceImport import JOIN Product p ON import.Product_ID = p.Product_ID LEFT JOIN Piece pc ON import.Piece_ID = pc.Piece_id WHERE pc.Piece_ID IS NULL A product has a one to many relationship with its pieces. One product can have multiple pieces. Piece_Key column which is not listed in the INSERT statement is an identity column and also a PK. Thanks for any help.
There is no anomaly and you are not going crazy either. You have a left join for a very good reason, namely to ensure that if there is existing Piece\_ID then this record is not included. However, there is nothing to prevent the records from the first join (tblInvoiceImport inner joined with Product) to include the same Piece\_ID of the import table for different Product\_Key records of the Product table. For example, try this to quickly reveal such records: select p.Product_Key, import.Piece_ID, row_number() over ( partition by import.Piece_ID order by p.Product_Key) N from tblInvoiceImport import inner join Product p on import.Product_ID = p.Product_ID left join Piece pc on import.Piece_ID = pc.Piece_id where pc.Piece_ID is null; Look at your results and if you see any records with N > 1 then this is your problem. How to solve it is a different story. It will depend on what do you want to do with the records where differnet Product Keys point to the same Piece. Once you make this decision then the problem can be solved. Oleg
here is the code that fixed this issue: insert into Piece select distinct Product_key, piece_id from ( select p.Product_Key,i.piece_id from tblInvoiceImport i left join Piece pc ON i.Piece_ID = pc.Piece_ID join Product p on p.Product_ID = i.Product_ID where pc.Piece_ID is null group by p.Product_Key, i.Piece_ID having COUNT(*) = 1 )a where piece_id ''
@Slick84 Yes, this is one way of doing it. It does mean that if different products point to the same piece then neither is included into results to be inserted. If this is by design then the problem is solved. If you still want to include such piece (assuming that it is not there already) then you can take the select in my answer and make a sub-select out of it choosing only those records which have N = 1. This way the new piece will still be inserted in combination with earlier product key (first wins).
