Do I need to specify ON DELETE NO ACTION on my Foreign Key?

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 NO ACTION

What I want is for the SQL Server to stop me deleting a parent if a reference to that parent exists in the child? For example I want a delete on subjectID=3 in Subject to fail if there are children with SubjectId's of 3.

For this I am unclear and cannot seem to find the answer. Do I need to add "DELETE NO ACTION" or can I just not remove these three words.

I'm asking this question as in a similar question I had a response that I should define a trigger on the parent. However I thought just defining the foreign key would stop me deleting the parent if a child exists.

score 62 · Accepted Answer · edited Jun 20 '20 at 09:12

62

From the column_constraint page on MSDN:

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

So, you can elide ON DELETE NO ACTION if you like and it will work just the same.

NO ACTION means that nothing will happen when you delete from your Subject table to the Topic table. In that case, if there is a row in Topic for a given SubjectId you cannot delete from it without breaking referential integrity, so the Delete will be rolled back.

More from MSDN:

NO ACTION - The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 31 '13 at 17:13

DaveShaw

52,123
16
112
141

Dave - I am sorry but I am a bit confused by "Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table." What does this mean as it relates to my Subject and Topic tables ? – Jul 31 '13 at 17:15
Yes Subject is the Parent and Topic is the child table. – DaveShaw Jul 31 '13 at 17:17
Dave - So am I correct in saying I don't need to code any DELETE ... keywords and it will not let me delete SubjectId=3 in the subject table if there's a topic with SubjectId=3 ? – Jul 31 '13 at 17:21
1

Correct. That's how foreign key's work. Every Topic must have a SubjectId in that matches a row in the Subject Table. If is now impossible to have a Topicless Subject. – DaveShaw Jul 31 '13 at 17:23

HLGEM · Answer 2 · 2017-10-17T17:24:55.540

I'm going to suggest to you that while you can skip the on delete no action, it might not be in your best interests to do so. Having this specified in the table definition might prevent someone later from adding an on cascade delete because they saw that you intended for that not to happen. This is particularly true when you correctly script all database objects and put them in source control and the code reviewer would see that there was a difference and ask why it happened. All too often , people are too eager to add on delete cascade and destroy data that should have been kept (like financial records for a customer who is no longer valid). They do this because they get the error that doesn't let them delete and just want to get rid of it instead of realizing that this is saving them from a massive mistake. At least if you have the code for Delete No Action in your table script, future maintainers will see that this was intentional and not just that you forgot to set up cascade deletes. Of course if your dba does not allow cascading deletes (as many do not and for good reason!) then this isn't a potential problem, but specifying your intent is often a good thing for maintainability.

score 0 · Answer 3 · answered Jul 24 '19 at 16:45

"ON DELETE NO ACTION" is in effect the same, as "ON DELETE".

I agree it would be good to use it to clarify the programmer thought about the required action when deleting a "header" and decided the "header" should not be deleted if there are "lines" related to it. However, I am defining my database in Visual Studio, and it refuses to update the change from "ON DELETE" to "ON DELETE NO ACTION". It is still "ON DELETE" when I refresh it.

score -3 · Answer 4 · answered Jul 31 '13 at 17:12

-3

You can remove the keywords (this is the default)

ON DELETE NO ACTION

Would be better to specify those actions

ON DELETE CASCADE

Here's more info this: http://msdn.microsoft.com/en-us/library/aa933119%28v=sql.80%29.aspx

You'll have to write a trigger to make sure child row's aren't deleted, as I dont think SQL Server has this option of ON DELETE RESTRICT

answered Jul 31 '13 at 17:12

Akash

4,956
11
42
70

Okay so are you saying having DELETE NO ACTION is the same as my not putting anything? DELETE RESTRICT is something new so I will try to check that out. – Jul 31 '13 at 17:14
Isn't Restrict Oracle only? – DaveShaw Jul 31 '13 at 17:16
@DaveShaw Yup, just realized it, I have updated the same, thanks – Akash Jul 31 '13 at 17:17
Akash - I tried my code without ON DELETE ... When I tried to delete a subject which had children it would not work. So do I really need a trigger? I am more confused now. – Jul 31 '13 at 17:17
1

@Akash, isn't that what you wanted in your question? Prevent Deletes. – DaveShaw Jul 31 '13 at 17:20
Yes I would like to simply prevent deletes on the subject if children still exist and reference it. – Jul 31 '13 at 17:22

Do I need to specify ON DELETE NO ACTION on my Foreign Key?

4 Answers4

Linked