sql-server - SQL Server 2005 中的不可见外键

Question

我有一个脚本可以通过检查外键是否存在来更新数据库，如果不存在，则创建它。它由 SQL Management Studio 生成。

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]'))
ALTER TABLE [dbo].[tblChangeRequestForecast]  WITH CHECK ADD  CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter] FOREIGN KEY([CostingCenterID])
REFERENCES [dbo].[tblCostingCenter] ([CostingCenterID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]'))
ALTER TABLE [dbo].[tblChangeRequestForecast] CHECK CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter]
GO

脚本在运行时引发错误:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblChangeRequestForecast_tblCostingCenter". The conflict occurred in database "mydatabase", table "dbo.tblCostingCenter", column 'CostingCenterID'.

这很神秘。就查询而言，没有外键的踪迹，但创建脚本因上述错误而失败。服务器正在运行 SQL Server 2005 SP3 (9.00.4035.00)。

[更新]:我刚刚在 SQL Server 2012 实例上重现了这个问题。所以版本似乎并不太重要。

知道是什么原因造成的吗？

Answer 1

它提示您尝试添加的 FK 约束，因为如果成功应用约束，表中的现有数据将不再有效。

可能意味着有一个或多个 ChangeRequestForecast 记录，其中 CostingCenterID 的值与 CostingCenter.CostingCenterID 不对应。