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

Answer 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.