SQL Server foreign key constraint error

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 Nov 01 '15 at 11:50 — Vahid Amiri, Nov 01 '15 at 11:50

score 0 · Accepted Answer · answered Nov 01 '15 at 11:55

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
)

SQL Server foreign key constraint error

1 Answers1