問題描述

我有 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 對所有子記錄執行刪除的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！