Our Training Programs

View all

AI & Machine Learning
Mastering Large Language Models
Mastering Prompt Engineering
Certified Vibe Coder
Github Copilot Training
Generative AI for Beginners
n8n Automation & AI Agents Training

About Us Contact Us Privacy Policy Terms Brand Guidelines Partners C# Tutorials Consultants Ideas Report A Bug FAQs Sitemap Stories CSharp TV DB Talks Web3 Universe Jumpstart Blockchain

All contents are copyright of their authors.

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

Home
Forums

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

Seshu B

8yAsked Nov 7, 2017, 10:22 AM10.1k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Nilesh Patil8y ago
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
Nilesh Shah8y ago
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.