How to delete data from two tables?

Question

I have two tables teacher and student. Different teachers have students. when I delete one teacher then student related to that teacher should also be deleted. Student have StudentTeacherId that relates to TeacherId in table teacher.

This is called Cascading.[ALTER TABLE table_constraint (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql?view=sql-server-2017) — Thom A, Jun 14 '19 at 09:58

Michał Turczyn · Answer 1 · 2019-06-14T10:01:43.403

1

There are couple of approaches:

If you are not using ORM:

Define cascade delete on foreign key constraint in SQL Server, then you need to send just delete query on Teacher table.
Create method which will get students based on teachers, then delete them, then delete teachers.

If you are using ORM:

1' In EF (and in other ORMs I guess) you can specify cascade delete, so ORM will handle for you situation, where you delete teachers.

2' Using ORM, get students' entities, delete them and then delete teachers

I think best for you will be option 1. as you didn't mention any ORMs. In this case read this: How do I use cascade delete with SQL Server?

edited Jun 14 '19 at 10:01

answered Jun 14 '19 at 09:58

Michał Turczyn

32,028
14
47
69

Do you really need this? This type of constraint should always be placed at DB end and never in your app layer. – Rahul Jun 14 '19 at 10:00
@Rahul I fully agree, I just presented different approaches, as every rule has its own exceptions :) – Michał Turczyn Jun 14 '19 at 10:01

score 0 · Answer 2 · edited Jun 14 '19 at 10:00

0

You will need to delete your current FK constraint and add a new one with ON DELETE CASCADE enabled.

Example:

ALTER TABLE Test
ADD CONSTRAINT fk_teacher
FOREIGN KEY (teacherID)
REFERENCES Test2 (teacherID)
ON DELETE CASCADE;

After when you delete a row from the teachers' table it will delete all the rows in the student one where you have a relationship

edited Jun 14 '19 at 10:00

Rahul

76,197
13
71
125

answered Jun 14 '19 at 09:59

Claudio Corchez

300
2
12

Giving error "Foreign key 'fk_teacher' references invalid column 'StudentTeacherId' in referencing table 'teacher'" – Ghost Jun 14 '19 at 10:15
@Ghost that you will have to figure out yourself. do some research .. read the documentation and stop expecting spoon feeding – Rahul Jun 14 '19 at 10:27
@Ghost this is an example, you did not provide data example so I used test parameters. You will have to use the one you have in your DB – Claudio Corchez Jun 14 '19 at 11:32

How to delete data from two tables?

2 Answers2