LEFT OUTER JOIN causing violation of unique key constraint

Question

I have two databases for two companies running on the same software, thus the DB structures are the same.

Windows Server 2003, MS SQL Sever 2005.

I'm attempting to copy the items list and associated tables from SOURCE to DESTINATION.

There are a total of 11 tables, I'm using the same format for the script to do the job on all 11. Three of them are failing.

The example below is one of the three:

--dbo.ITEM_MEASURE 5
SET IDENTITY_INSERT DESTINATION.dbo.ITEM_MEASURE ON

INSERT DESTINATION.dbo.ITEM_MEASURE(
  ITEM_MEASURE_ID, MEAS_TYPE, ITEMNO, MEAS_CODE, SELLPRIC, MARKUP, S_PERC_DOC, 
  MIN_AMOUNT, COSTPERSP, COST, COST_LOW, COST_HIGH, WEIGHT_MEAS, WEIGHT,
  SIZE_MEAS, LENGTH, BREADTH, HEIGHT, VOLUME_MEAS, VOLUME, LAST_COST)
SELECT s.ITEM_MEASURE_ID, s.MEAS_TYPE, s.ITEMNO, s.MEAS_CODE, s.SELLPRIC,
  s.MARKUP, s.S_PERC_DOC, s.MIN_AMOUNT, s.COSTPERSP, '0', '0', '0' ,
  s.WEIGHT_MEAS, s.WEIGHT, s.SIZE_MEAS, s.LENGTH, s.BREADTH, s.HEIGHT,
  s.VOLUME_MEAS, s.VOLUME, '0'
FROM SOURCE.dbo.ITEM_MEASURE s
  LEFT OUTER JOIN DESTINATION.dbo.ITEM_MEASURE d
    ON (d.ITEM_MEASURE_ID = s.ITEM_MEASURE_ID)
WHERE d.ITEM_MEASURE_ID IS NULL

SET IDENTITY_INSERT DESTINATION.dbo.ITEM_MEASURE OFF

/* ERROR
Msg 2627, Level 14, State 1, Line 73
Violation of UNIQUE KEY constraint 'IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEAS'. Cannot insert duplicate key in object 'dbo.ITEM_MEASURE'.
The statement has been terminated.
*/

The table PK is ITEM_MEASURE_ID, there are no duplicates in either SOURCE or DESTINATION separately and as I understood it the "WHERE d.ITEM_MEASURE_ID IS NULL" statement prevented it from trying to copy data into DESTINATION which already exists.

Why am I getting this error?

UPDATE - Can't post images, but here is a look at the constraints and indexes.:
https://photos-6.dropbox.com/t/1/AAD2EzrJTZFy_BMqcL5i2dWmZn1bAp5C7Y6LAHwJZ1btYQ/12/1501690/png/1024x768/3/1415138400/0/2/constraint.png/vvHTaOuDXOO72MN7IYeDnbLzAjQ65deom5zF9GV3jgw

UPDATE - Properties on IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEASURE: https://photos-3.dropbox.com/t/1/AAC8eurM2o8SfHfvLNOsvwt8h_2P_qGpvRBmhovIp3cJzg/12/1501690/png/1024x768/3/1415142000/0/2/properties.PNG/Lf4Q_hE1QTsHgEI1BAxR9WoyL2R71MPFxDZJ5R9kXN0

What is the definition of the UNIQUE KEY constraint 'IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEAS'? — DeanOC, Nov 04 '14 at 19:51
Please answer @DeanOC's question, since the most likely problem is that there's another unique key other than the primary key — Lamak, Nov 04 '14 at 20:01
I'm sorry to report, I don't understand the question, or how to answer it. I'm as green as it comes with regard SQL, this is my first attempt at understanding this stuff. — todbanner, Nov 04 '14 at 20:10
Under design on dbo.ITEM_MEASURE the only key showing is the PK — todbanner, Nov 04 '14 at 20:10
@todbanner But the error message clearly says: `UNIQUE KEY constraint 'IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEAS'`. So, go to the indexes of the table and see if there is a unique index there. Or a constraint — Lamak, Nov 04 '14 at 20:20
@todbanner Well, there's an index named exactly `IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEAS`, so, what are its properties? — Lamak, Nov 04 '14 at 20:53
@todbanner And the image shows that the `unique` checkbox is, in fact, checked. So, the combination of `ITEMNO, MEAS_TYPE, MEAS_CODE` must be unique — Lamak, Nov 04 '14 at 21:13
@Lamak, so forgive my ignorance, but how do I script to check for combinations of three vs just checking ITEM_MEASRURE_ID = NULL? — todbanner, Nov 04 '14 at 21:16

John Bollinger · Accepted Answer · 2014-11-04T21:03:15.047

1

It seems most likely that the affected tables (and maybe others) have one or more UNIQUE constraints (or unique indices) in addition to their PKs. For example, the name of the violated constraint suggests that it might be a constraint such as

UNIQUE(ITEMNO, MEAS_TYPE)

... or a unique index on such a collection of columns. There is no particular reason to suppose that in two unrelated databases, equal (ITEMNO, MEAS_TYPE) pairs (or any other non-PK data) would be associated with the same PKs, therefore your strategy to avoid PK violations is not certain to avoid violations of a constraint such as this.

For that matter, you haven't presented any reason to believe that PKs in your source and destination tables are correlated in any way. Although it may make the table copy proceed without error, I see no reason to believe that the query you present is actually doing the right thing.

Edited to add: In fact, it looks like ITEM_MEASURE_ID is a surrogate key (i.e. one invented by the application or DBMS, independent of any of the data). It is extremely unlikely that matching source data to independently recorded destination data by such a key will give you meaningful results (not in other tables, either). You should use a natural key instead, such as (ITEMNO, MEAS_TYPE) if that indeed suits. Where there are UNIQUE constraints or unique indices on your tables they may serve as a clue to the natural keys. For this table in isolation, that might look like this instead:

-- NOTE: NOT inserting values for the IDENTITY column

INSERT DESTINATION.dbo.ITEM_MEASURE(
  MEAS_TYPE, ITEMNO, MEAS_CODE, SELLPRIC, MARKUP, S_PERC_DOC, 
  MIN_AMOUNT, COSTPERSP, COST, COST_LOW, COST_HIGH, WEIGHT_MEAS, WEIGHT,
  SIZE_MEAS, LENGTH, BREADTH, HEIGHT, VOLUME_MEAS, VOLUME, LAST_COST)
SELECT s.MEAS_TYPE, s.ITEMNO, s.MEAS_CODE, s.SELLPRIC,
  s.MARKUP, s.S_PERC_DOC, s.MIN_AMOUNT, s.COSTPERSP, '0', '0', '0' ,
  s.WEIGHT_MEAS, s.WEIGHT, s.SIZE_MEAS, s.LENGTH, s.BREADTH, s.HEIGHT,
  s.VOLUME_MEAS, s.VOLUME, '0'
FROM SOURCE.dbo.ITEM_MEASURE s
  LEFT OUTER JOIN DESTINATION.dbo.ITEM_MEASURE d
    ON (d.ITEMNO = s.ITEMNO) AND (d.MEAS_TYPE = s.MEAS_TYPE)
WHERE d.ITEMNO IS NULL

That gets a lot messier if you need to deal with related source tables, because PKs in the destination tables differ from PKs in the source tables, but it can be done.

edited Nov 04 '14 at 21:03

answered Nov 04 '14 at 20:02

John Bollinger

160,171
8
81
157

I think I understand what you're saying, that you're not certain executing this will result in the desired effect. As I said in the question, this is one of eleven tables I'm copying across and eight of the eleven execute with no errors. Once I have all eleven across, this will result in the ITEMS lists in the two company databases being sync'd. – todbanner Nov 04 '14 at 20:15
If the PKs of the tables at the two companies are not correlated, then the result of your query is *garbage*, not synchronized tables. That is, where the same PK appears in both tables, if the rest of the row may differ from one table to the other then your approach is incorrect. In that case, the constraint violation you encountered is a lucky warning trying to clue you in to the problem in your methodology. – John Bollinger Nov 04 '14 at 20:20
I will have a look at the data in the two tables. In theory all the part numbers in DESTINATION currently are the same as those in SOURCE. Therefore the ITEM_MEASURE_ID should be the same in SOURCE AND DESTINATION for records which exist in both. I will double check this now. – todbanner Nov 04 '14 at 20:33
Correlation of `ITEM_MEASURE_ID`s does not follow from using the same value space for some other column (maybe `ITEMNO`?). In fact, if those IDs are assigned automatically then it is highly unlikely that they are correlated, as that would require equal records to have been inserted into each table in the same order. – John Bollinger Nov 04 '14 at 20:41
I've edited my answer to add a possible solution, applicable on a single-table basis. As I note there, I think this problem is likely a lot messier than you have accounted for. – John Bollinger Nov 04 '14 at 20:59
the problem is that each item number has multiple entries in this table, therefore the requirement for ITEM_MEASURE_ID. I understand what you're saying that this key is potentially not the same as the one in the SOURCE. However, if I don't insert the ITEM_MEASURE_ID I don't understand how the DESTINATION DB will associate the data in this table with the records in other tables pertaining to item numbers. – todbanner Nov 04 '14 at 21:13
It appears that the SYSTEM didn't need the ITEM_MEASURE_ID importing. Therefore this solution solved it! – todbanner Nov 04 '14 at 22:20
I'm glad it works for you. Yes, `ITEM_MEASURE_ID` is apparently an `IDENTITY` column, so values are generated for it automatically if you do not specify them. BE AWARE, however, that the new IDs in the destination table will be *different* from those in the source table. This will break any relationships depending on those IDs. That's the messiness that I warn about. It can be handled if necessary. – John Bollinger Nov 04 '14 at 22:26

score 0 · Answer 2 · answered Nov 04 '14 at 19:51

0

First thing I'd try is making a select statement to see what the conflict is.

SELECT * FROM DESTINATION.dbo.ITEM_MEASURE
WHERE ITEM_MEASURE_ID IN (SELECT s.ITEM_MEASURE_ID FROM SOURCE.dbo.ITEM_MEASURE s
 LEFT OUTER JOIN DESTINATION.dbo.ITEM_MEASURE d ON (d.ITEM_MEASURE_ID = s.ITEM_MEASURE_ID)
WHERE d.ITEM_MEASURE_ID IS NULL)

This should show you what is conflicting. Other than that, is it possible that DESTINATION.dbo.ITEM_MEASURE has a duplicate?

answered Nov 04 '14 at 19:51

LeeG

708
5
14

The question already specifies that there are no duplicates in either table. – John Bollinger Nov 04 '14 at 19:53
The query you suggested above returns nothing. However if I open the two tables side by side in there are 1200+ more records in the SOURCE which should be getting copied to the DESTINATION. I opened both tables in excel, sorted and removed duplicates. Both resulted in No Duplicates. So I believe that ruled that out. – todbanner Nov 04 '14 at 19:57

Dan Bracuk · Answer 3 · 2014-11-04T20:35:11.077

0

This is a formatted comment. What happens when you run this?

select item_measure_id, count(*) records
from (SELECT s.ITEM_MEASURE_ID, s.MEAS_TYPE, s.ITEMNO, s.MEAS_CODE, s.SELLPRIC,
s.MARKUP, s.S_PERC_DOC, s.MIN_AMOUNT, s.COSTPERSP, '0', '0', '0' ,
s.WEIGHT_MEAS, s.WEIGHT, s.SIZE_MEAS, s.LENGTH, s.BREADTH, s.HEIGHT,
s.VOLUME_MEAS, s.VOLUME, '0'
FROM SOURCE.dbo.ITEM_MEASURE s
LEFT OUTER JOIN DESTINATION.dbo.ITEM_MEASURE d
ON (d.ITEM_MEASURE_ID = s.ITEM_MEASURE_ID)
WHERE d.ITEM_MEASURE_ID IS NULL
) temp
group by item_measure_id
having records > 1
order by records desc

edited Nov 04 '14 at 20:35

answered Nov 04 '14 at 19:57

Dan Bracuk

20,699
4
26
43

Msg 8155, Level 16, State 2, Line 21 No column was specified for column 10 of 'temp'. Msg 8155, Level 16, State 2, Line 21 No column was specified for column 11 of 'temp'. Msg 8155, Level 16, State 2, Line 21 No column was specified for column 12 of 'temp'. Msg 8155, Level 16, State 2, Line 21 No column was specified for column 21 of 'temp'. Msg 207, Level 16, State 1, Line 31 Invalid column name 'records'. – todbanner Nov 04 '14 at 20:02
Oops, missed a group by clause. – Dan Bracuk Nov 04 '14 at 20:35
Would you like me to run something different? – todbanner Nov 04 '14 at 20:52

LEFT OUTER JOIN causing violation of unique key constraint

3 Answers3