我需要在我的外键上指定 ON DELETE NO ACTION 吗? [英] Do I need to specify ON DELETE NO ACTION on my Foreign Key?

问题描述

我有以下用于 SQL Server 2012 的 DDL:

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

我想要的是，如果子项中存在对该父项的引用，SQL Server 将阻止我删除该父项?例如，如果存在 SubjectId 为 3 的孩子，我希望在 Subject 中删除 subjectID=3 失败.

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.

对此我不清楚，似乎无法找到答案.我需要添加DELETE NO ACTION"还是我可以不删除这三个词.

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.

推荐答案

来自 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.

因此，如果您愿意，您可以省略ON DELETE NO ACTION，它的工作原理相同.

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

NO ACTION 意味着当您从主题表中删除到主题表时不会发生任何事情.在这种情况下，如果给定 SubjectId 的 Topic 中有一行，您不能在不破坏参照完整性的情况下从中删除，因此删除将被回滚.

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.

更多来自 MSDN:

NO ACTION - SQL Server 数据库引擎引发错误并且回滚父表中行的删除操作.

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

这篇关于我需要在我的外键上指定 ON DELETE NO ACTION 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！