I'm kind of new to scripting in SQL and I have encountered an error in one of my scripts.
The problematic section is:
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'.
Can someone please tell me where I'm going wrong?
Best Answer
The default value of 1 that you've specified for the
Workspace.WorkspaceSettingsId
column does not yet exist in yourWorkspaceSettings
table, hence the FK violation.