Sql Server Perform A Delete On All Child Records When Deleting From A Parent

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

Solution 1:

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

CreateTABLE Subjects (
    SubjectID INTPRIMARY KEY NOTNULLIDENTITY(1, 1),
    SubjectName VARCHAR(20) NOTNULL,
    ClassID VARCHAR(10) NOTNULLFOREIGN KEY REFERENCES Classes(ClassID) ONDELETE 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.

Solution 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.

CREATETABLE Topic
( 
    TopicID INTPRIMARY KEY NOTNULLIDENTITY(1,1),
    TopicName VARCHAR(100),
    SubjectID INT, 
    CONSTRAINT FK_Subjects_Topic FOREIGN KEY (SubjectID)
        REFERENCES Subjects (SubjectID) 
        ONDELETE CASCADE
        ONUPDATENO ACTION
)

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

CreateTable Worksheet
(
    WorksheetName varchar(100) PRIMARY KEY,
    TopicID INT,
    Num_Q INTNOTNULL,
    W_Type varchar(30),
    CONSTRAINT FK_Topic_Worksheet FOREIGN KEY (TopicID)
        REFERENCES Topic (TopicID) 
        ONDELETE CASCADE
        ONUPDATENO ACTION
);

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