how to delete parent and child records at time in Sql server

Question

hi all I want to delete records from parent and child tables when there is foreign key relation between parent and child tables using stored procedure thanks

Nilesh Shah · Answer

if there is already foreign key relationship then you don't need to write any specific code to delete child records when a master record is deleted. simply specify On delete Cascade in foreign key then SQL Server will do it automatically for you. example of a foreign key with delete cascade: ALTER TABLE dbo.ChildTable ADD CONSTRAINT FK_With_Cascade FOREIGN KEY (EmployeeID) REFERENCES dbo.MasterTable(EmployeeID) ON DELETE CASCADE I suggest you read more on MSDN on this.

Nilesh Patil · Answer

Hi Seshu 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 ); Thank you