SQL Server foreign key constraint error [duplicate]

Question

I have the following table:

CREATE TABLE [dbo].[Comment]
(
    [CommentID] INT NOT NULL PRIMARY KEY, 
    [CommentContent] NVARCHAR(MAX) NULL, 
    [CommentDateTime] DATETIME2 NULL, 
    [ArticleID] INT NULL,
    [ResponseTo] INT NULL, 

    CONSTRAINT [FK_dbo.Comment_dbo.Article_ArticleID] 
        FOREIGN KEY ([ArticleID]) 
        REFERENCES [dbo].[Article] ([ArticleID]) ON DELETE CASCADE,

    CONSTRAINT [FK_dbo.Comment_dbo.Comment_ResponseTo] 
        FOREIGN KEY ([ResponseTo]) 
        REFERENCES [dbo].[Comment] ([CommentID]) ON DELETE CASCADE
)

And I get this error about not being able to create the constraint:

Error: SQL72014: .Net SqlClient Data Provider:
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_dbo.Comment_dbo.Comment_ResponseTo' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

It says something about cycles... how do I fix it?

TL;DR, how do fix this?? removing the 'CASCADE'??

Vahid Amiri
– Vahid Amiri

2015-11-01 11:50:11 +00:00
Commented Nov 1, 2015 at 11:50 — Vahid Amiri
– Vahid Amiri, Commented Nov 1, 2015 at 11:50

Vahid Amiri · Accepted Answer · 2015-11-01 11:55:01Z

0

Changing CASCADE to NO ACTION worked.

CREATE TABLE [dbo].[Comment]
(
    [CommentID] INT NOT NULL PRIMARY KEY, 
    [CommentContent] NVARCHAR(MAX) NULL, 
    [CommentDateTime] DATETIME2 NULL, 
    [ArticleID] INT NULL,
    [ResponseTo] INT NULL, 
    CONSTRAINT [FK_dbo.Comment_dbo.Article_ArticleID] FOREIGN KEY ([ArticleID]) 
        REFERENCES [dbo].[Article] ([ArticleID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Comment_dbo.Comment_ResponseTo] FOREIGN KEY ([ResponseTo]) 
        REFERENCES [dbo].[Comment] ([CommentID]) ON DELETE NO ACTION
)

answered Nov 1, 2015 at 11:55

Vahid Amiri

11.2k13 gold badges75 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server foreign key constraint error [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related