SQL ALTER TABLE 语句与 FOREIGN KEY 约束冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4865873/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The ALTER TABLE statement conflicted with the FOREIGN KEY constrain
提问by ErnieStings
I'm kind of new to scripting in SQL and I have encountered an error in one of my scripts. The problematic section is:
我是 SQL 脚本的新手，我在我的一个脚本中遇到了错误。有问题的部分是：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.WorkspaceSettings
(
Id INT NOT NULL IDENTITY PRIMARY KEY ,
ReportColorRGB1 VARCHAR(15) NOT NULL DEFAULT '61,105,138' ,
ReportColorRGB2 VARCHAR(15) NOT NULL DEFAULT '180,210,121'
)
GO
ALTER TABLE Workspace ADD WorkspaceSettingsId int NOT NULL default 1;
GO
ALTER TABLE Workspace
ADD CONSTRAINT FK_WorkspaceSettings_Workspace
FOREIGN KEY (WorkspaceSettingsId)
REFERENCES WorkspaceSettings(Id);
GO
And receive the following error message:
并收到以下错误消息：
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_WorkspaceSettings_Workspace". The conflict occurred in database "ClearRisk2_0", table "dbo.WorkspaceSettings", column 'Id'.
消息 547，级别 16，状态 0，第 1 行
ALTER TABLE 语句与 FOREIGN KEY 约束“FK_WorkspaceSettings_Workspace”冲突。冲突发生在数据库“ClearRisk2_0”、表“dbo.WorkspaceSettings”、“Id”列中。
Can someone please tell me where I'm going wrong?
有人可以告诉我我哪里出错了吗？
回答by Joe Stefanelli
The default value of 1 that you've specified for the Workspace.WorkspaceSettingsIdcolumn does not yet exist in your WorkspaceSettingstable, hence the FK violation.
您为该Workspace.WorkspaceSettingsId列指定的默认值 1尚不存在于您的WorkspaceSettings表中，因此违反 FK。
回答by Mohsen Abasi
Just add the following phrase after ALter table sattement:
只需在 ALter table sattement 后添加以下短语：
with nocheck
So, it will be:
所以，它将是：
Use Database_name
Go
ALTER TABLE ResultScan with nocheck
ADD CONSTRAINT FK_ResultScan_ListVM FOREIGN KEY (TypeAnVirus)
REFERENCES ListVM (Id)
ON DELETE CASCADE
ON UPDATE CASCADE
;
GO
