CODEWITHSUNDEEP
sqlsql-servert-sql
ErnieStings
ErnieStings

Reputation: 6423

The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

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?

Upvotes: 16

Views: 47050

Answers (2)

محسن عباسی
محسن عباسی

Reputation: 2454

Just add the following phrase after 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

Upvotes: -1

Joe Stefanelli
Joe Stefanelli

Reputation: 135918

The default value of 1 that you've specified for the Workspace.WorkspaceSettingsId column does not yet exist in your WorkspaceSettings table, hence the FK violation.

Upvotes: 39

Related Questions