How can I stop the delete on a parent if a child entity that references that parent exists?

Question

I have the following DDL that I am using with SQL Server 2012:

CREATE TABLE Subject (
   [SubjectId] INT IDENTITY (1, 1) NOT NULL,
   [Name] NVARCHAR (50) Not NULL,
   CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED ([SubjectId] ASC)
)           

CREATE TABLE Topic (
   [TopicId] INT IDENTITY (1, 1) NOT NULL,
   [Name] NVARCHAR (50) NOT NULL,
   [SubjectId] INT NOT NULL,
   CONSTRAINT [PK_Topic] PRIMARY KEY CLUSTERED ([TopicId] ASC)
)
ALTER TABLE [Topic] WITH CHECK ADD  CONSTRAINT [FK_TopicSubject] 
   FOREIGN KEY([SubjectId]) REFERENCES [Subject] ([SubjectId]) 
   ON DELETE CASCADE

CREATE TABLE SubTopic (
   [SubTopicId] INT IDENTITY (1, 1) NOT NULL,
   [TopicId] INT NOT NULL,
   [Name] NVARCHAR (4000) Not NULL,
   CONSTRAINT [PK_SubTopic] PRIMARY KEY CLUSTERED ([SubTopicId] ASC)
)

ALTER TABLE [SubTopic] WITH CHECK ADD  CONSTRAINT [FK_SubTopicTopic] 
   FOREIGN KEY([TopicId]) REFERENCES [Topic] ([TopicId]) 
   ON DELETE CASCADE

When I try to run the scripts I get the following message:

{"Introducing FOREIGN KEY constraint 'FK_TopicSubject' 
on table 'Topic' may cause cycles or multiple cascade paths. 
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, 
or modify other FOREIGN KEY constraints.\r\nCould not create constraint. 
See previous errors."}

What I really need is for when a person tries to DELETE a subject when there are topics for the delete to fail. If I include neither DELETE ON CASCADE or DELETE NO ACTION then will this happen. If not then how can I stop the delete on subject happening if there are Topics for that subject?

I am still not clear on this after looking at several places. Is there a difference between "ON DELETE NO ACTION" and my not including anything about DELETE on that line ? — , Jul 31 '13 at 16:43

score 1 · Answer 1 · answered Aug 01 '13 at 10:00

Short answer is: If you don’t want cascade updates and deletions then use ON DELETE NO ACTION. Same applies for Update.

Here is a copy from MSDN article (it’s SQL Server 2000 but same rules still apply)

ON DELETE NO ACTION

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.

ON UPDATE NO ACTION

Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back.

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Please refer to this link. It has given a detail explanation of this error, and also has suggested to create a trigger as an alternative. Foreign key constraint may cause cycles or multiple cascade paths?

edited May 23 '17 at 12:20

Community

1
1

answered Jul 31 '13 at 16:23

Sonam

3,406
1
12
24

1

Also I would like to add that you are getting this error because, when you delete a row from the Subject table, SQL server will delete a row from the Topics table(on Delete cascade) and then it will try to delete a row from the sub topics table. So here you can see that the system is just counting the cascades path and to avoid any worst cases its trying to avoid any multiple levels Cascade. – Sonam Jul 31 '13 at 16:29
Thanks for your explanation. If I do not have "ON DELETE CASCADE" and there is still something in the Topics table when I try to delete a subject then will that result in an error and failed DELETE ? – Jul 31 '13 at 16:41
Do I need to have either "ON DELETE CASCADE" or "ON DELETE NO ACTION" Can I just not have either ? – Jul 31 '13 at 16:44
You can go for that option but that would result in inconsistent data. – Sonam Jul 31 '13 at 16:52
So to avoid any inconsistency you can define a trigger to delete a row from the child table, or add an additional bit type column in your child table and set it to 0 or 1 when the row is deleted from the parent table and query it accordingly. – Sonam Jul 31 '13 at 16:54
Do you mean a trigger that will check to see if child records exist before allowing the parent to delete? I am sorry I don't understand about the 0 or 1. Can you add that to your answer with just a bit more details. Thanks – Jul 31 '13 at 16:55
Yes, you can create a trigger to delete a record from Child table before parent table. Also you can go for option to not have Parent child relationship between tables then you can add a bit type column in your child table and by default its value would be 1. So when a value is deleted from the parent table you can turn that value to 0, and whenever you query the child table always look for rows with value as 1. But this option doesn't guarantee referential integrity and you may end up with some inconsistency. – Sonam Jul 31 '13 at 17:37

How can I stop the delete on a parent if a child entity that references that parent exists?

2 Answers2