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

Question

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

Windows Server 2003，MS SQL Sever 2005。

我正试图将项目列表和相关表格从SOURCE复制到DESTINATION。

总共有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，SOURCE或DESTINATION中分别没有重复项，据我所知，"WHERE d.ITEM_MEAURE_ID为NULL"语句阻止它尝试将数据复制到已经存在的DESTINATION。

为什么我会出现此错误？

UPDATE-无法发布图像，但下面是约束和索引。：
https://photos-6.dropbox.com/t/1/AAD2EzrJTZFy_BMqcL5i2dWmZn1bAp5C7Y6LAHwJZ1btYQ/12/1501690/png/1024x768/3/1415138400/0/2/constraint.png/vvHTaOuDXOO72MN7IYeDnbLzAjQ65deom5zF9GV3jgw

UPDATE-IN_ITEM_MEASURE_UN_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与源表中的PKs不同，但这是可以做到的。

Answer 2

我首先尝试做一个select语句来查看冲突是什么。

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_EASURE是否可能有重复？

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