从父级删除时，SQL Server 对所有子记录执行删除 [英] SQL Server perform a delete on all child records when deleting from a parent

问题描述

我有 3 张桌子:

Create TABLE Subjects
(
    SubjectID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    SubjectName VARCHAR(20) NOT NULL,
    ClassID VARCHAR(10) FOREIGN KEY REFERENCES Classes(ClassID) NOT NULL
);

Create TABLE Topic
( 
    TopicID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    TopicName VARCHAR(100),
    SubjectID INT FOREIGN KEY REFERENCES Subjects(SubjectID)
); 

Create Table Worksheet
(
    WorksheetName varchar(100) PRIMARY KEY,
    TopicID INT Foreign KEY References Topic(TopicID),
    Num_Q INT NOT NULL,
    W_Type varchar(30)
);

每一个都是一对多的关系.当我尝试从 Subjects 中删除时，我得到了一个很好的外键约束.我想知道的是如何解决这个问题并执行查询以级联样式删除所有相关方面.我查了一下，但我不确定它是如何工作的，似乎有多个查询.创建触发器会更好还是有一个基本的级联功能来完成所有工作?我正在使用 Visual Studio 执行查询，但不确定执行此类任务的选项在哪里?

Each one is a one to many relationship. When I try to delete from Subjects I get a foreign key constraint which is fine. What I want to know is how to get around this and perform a query to delete all relating aspects in a cascading style. I looked it up and there's but I am not sure how it works there seems to be multiple queries. Would it be better to create a trigger or is there a basic cascading function to do it all? I'm using visual studio to perform queries but not sure where the options to perform tasks like this are?

推荐答案

您可以在外键定义之后添加ON DELETE CASCADE:

You can add the ON DELETE CASCADE right after the foreign key definition:

Create TABLE Subjects (
    SubjectID INT PRIMARY KEY NOT NULL IDENTITY(1, 1),
    SubjectName VARCHAR(20) NOT NULL,
    ClassID VARCHAR(10) NOT NULL
        FOREIGN KEY REFERENCES Classes(ClassID) ON DELETE CASCADE
);

如果愿意，您也可以将其定义为单独的约束，在 CREATE TABLE 语句中或使用 ALTER TABLE ADD CONSTRAINT.

You can also define it as a separate constraint, if you like, either within the CREATE TABLE statement or using ALTER TABLE ADD CONSTRAINT.

这篇关于从父级删除时，SQL Server 对所有子记录执行删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！