SQL Server perform a delete on all child records w

问题:

I have 3 tables:

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)
);

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?

回答1:

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
);

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

回答2:

Here is the DDL for your Topic table with a CASCADE for delete. Its just a matter of defining it in your FK but using a slightly different syntax. This is for MS Sql Server.

CREATE TABLE Topic
( 
    TopicID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    TopicName VARCHAR(100),
    SubjectID INT, 
    CONSTRAINT FK_Subjects_Topic FOREIGN KEY (SubjectID)
        REFERENCES Subjects (SubjectID) 
        ON DELETE CASCADE
        ON UPDATE NO ACTION
)

EDIT - added DELETE CASCADE on Worksheet table based on comment feedback.

Create Table Worksheet
(
    WorksheetName varchar(100) PRIMARY KEY,
    TopicID INT,
    Num_Q INT NOT NULL,
    W_Type varchar(30),
    CONSTRAINT FK_Topic_Worksheet FOREIGN KEY (TopicID)
        REFERENCES Topic (TopicID) 
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

With this updated definition a delete on table Subjects will also delete child records in table Topics.