生产环境缺失复合外键创建失败，查询脚本能否识别孤儿行？

问题背景 #

生产环境中，尝试为dbo.tbl_ComputationWebsheetCellData表添加复合外键时，报错：Alter table conflicted with foreign key constraint。

添加约束的SQL语句：

ALTER TABLE dbo.tbl_ComputationWebsheetCellData WITH CHECK ADD CONSTRAINT [FK_tbl_ComputationWebsheetCellData_tbl_ComputationWebsheetCell] 
        FOREIGN KEY (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID) 
        REFERENCES [tbl_ComputationWebsheetCell] (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)

表结构详情 #

tbl_ComputationWebsheetCellData #

• 主键：PK_tbl_ComputationWebsheetCellData (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)
• 待添加外键：FK_tbl_ComputationWebsheetCellData_tbl_ComputationWebsheetCell，关联dbo.tbl_ComputationWebsheetCell的(ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)

tbl_ComputationWebsheetCell #

• 主键：PK_tbl_ComputationWebsheetCell (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)

疑问 #

以下查询脚本能否找出孤儿行？

select top 10 celldata.*
 from tbl_ComputationWebsheetCellData (nolock) celldata
 left join tbl_ComputationWebsheetCell(nolock) cell
 On celldata.ComputationID = cell.ComputationID
 and celldata.CustomScheduleID = cell.CustomScheduleID
 and celldata.AccountGUID = cell.AccountGUID
 and celldata.ColumnGUID = cell.ColumnGUID
 where cell.ComputationID IS NULL
 and cell.CustomScheduleID IS NULL
 and cell.AccountGUID Is NULL
 and cell.ColumnGUID IS NULL

回答 #

这个查询可以找出孤儿行，但写法存在冗余。

逻辑上，左连接后，若tbl_ComputationWebsheetCell中无匹配行，该表所有字段都会返回NULL。原查询通过判断四个主键字段全为NULL，确实能筛选出tbl_ComputationWebsheetCellData中在关联表找不到对应记录的行。

不过更简洁的写法是仅判断cell表任意一个主键字段为NULL即可——因为主键字段本身不允许为NULL，只要其中一个主键字段为NULL，就说明没有匹配行，无需逐一校验所有四个字段：

select top 10 celldata.*
from tbl_ComputationWebsheetCellData (nolock) celldata
left join tbl_ComputationWebsheetCell(nolock) cell
    On celldata.ComputationID = cell.ComputationID
    and celldata.CustomScheduleID = cell.CustomScheduleID
    and celldata.AccountGUID = cell.AccountGUID
    and celldata.ColumnGUID = cell.ColumnGUID
where cell.ComputationID IS NULL

另外注意：(nolock)提示可能读取到未提交的脏数据，生产环境使用需谨慎评估风险。

内容的提问来源于stack exchange，提问作者omkar