LEFT OUTER JOIN导致违反唯一键约束

Question

我有两个公司在同一软件上运行的两个数据库，因此DB结构是相同的。

的Windows Server 2003，MS SQL Sever的2005

我试图项目列表和相关表格从SOURCE复制到目的地。

总共有11个表格，我使用脚本的相同格式来完成11个工作。其中三个失败。

下面的例子是三个中的一个：

--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. 
*/ 

表PK是ITEM_MEASURE_ID，有在任一源或目的地无重复分别与我的理解它的“WHERE d.ITEM_MEASURE_ID IS NULL”语句阻止它试图将数据复制到已存在的DESTINATION中。

为什么我会收到此错误？

更新 - 不能发表图片，但这里是一个看的约束和索引：
https://photos-6.dropbox.com/t/1/AAD2EzrJTZFy_BMqcL5i2dWmZn1bAp5C7Y6LAHwJZ1btYQ/12/1501690/png/1024x768/3/1415138400/0/2/constraint.png/vvHTaOuDXOO72MN7IYeDnbLzAjQ65deom5zF9GV3jgw

更新 - 在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

似乎最有可能受影响的表（或其它）除了拥有自己的PK一个或多个UNIQUE约束（或唯一索引）。例如，违反约束的名称表明它可能是一个约束条件，如

UNIQUE(ITEMNO, MEAS_TYPE) 

...或这样的列集合的唯一索引。没有什么特别的理由可以假设，在两个不相关的数据库中，相同的（ITEMNO，MEAS_TYPE）对（或任何其他非PK数据）将与相同的PK相关联，因此，您避免PK违反的策略并不一定能够避免违规像这样的约束。

对于这个问题，你没有提出任何理由相信源表和目标表中的PK以任何方式相关。虽然它可能会使表复制无误地进行，但我没有理由相信您提出的查询实际上是在做正确的事情。

编辑补充： 事实上，它看起来像ITEM_MEASURE_ID是代理键（即，一个应用程序或DBMS，独立于任何的数据的发明）。通过这种密钥将源数据与独立记录的目标数据进行匹配极其不可能得到有意义的结果（也不在其他表中）。您应该使用自然键，例如(ITEMNO, MEAS_TYPE)，如果确实适合。在桌子上有UNIQUE约束或唯一索引，它们可以作为自然键的线索。对于此表中隔离，这可能是这样的，而不是：

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

，如果你需要处理相关的源表，因为在目标表的PK从的PK源表中的不同得到了很多混乱，但可以办到。

Answer 2

我会尝试的第一件事是做一个选择语句以查看冲突是什么。

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) 

这应该告诉你什么是冲突。除此之外，是否有可能DESTINATION.dbo.ITEM_MEASURE有重复？

Answer 3

这是一个格式化的评论。当你运行这个时会发生什么？

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