Do I need to specify ON DELETE NO ACTION on my Foreign Key?
sql, sql-server, sql-server-2012
Solution
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.
Problem
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.