Hey guys, I am Venkat and in this video, we'll answer an interview question faced by one of our
Youtube channel subscribers in a sql interview. To be able to answer this sequel question and
any related follow-up questions, you need to have a good understanding of foreign key constraints,
cascading deletes and transactions. We discussed these concepts in detail in this sql server
tutorial for beginners course. I'll include the link in the description of this video.
Now, the question in the interview goes like this, we have two tables - Table A and Table B.
If I delete a row from Table A, all the related rows in Table B must also get deleted, how do we
achieve this in sql server? To give it a bit more context and clarity, instead of Table A and Table
B, let's use Departments and Employees tables. When a row from Departments table is deleted,
all the related rows from the Employees table must also be deleted. For example, if we delete
the "IT" department row from the Departments table, we also want all the employees of the
IT department to be deleted from the Employees table as well. Now, here is the important bit to
keep in mind, "DeptId" column in the Employees table is a foreign key referencing "Id" column
in the Departments table. So, when a row is deleted from the Departments table,
we also want all that department employees to be deleted from the Employees table. Considering
the fact that "DeptId" is a foreign key, the correct way to achieve this is by enforcing
cascade deletes. Let's look at this in action. We have the sql script here to create both the
tables - Departments and Employees. Notice, "DeptId" in our Employees table is a foreign
key referencing "Id" column from the Departments table. In the interest of time, I've already
executed this sql script. I'll have this script available in the description of this video. Now,
let's execute these two queries so we can take a look at the data we have in these two tables.
Notice, both these employees, Mark and Mary are from IT department because their DeptId is 1. Now,
let's try to delete this IT department row from Departments table and see what's going to happen.
Notice, the delete statement failed with foreign key constraint reference error and
this kind of makes sense because in the Employees table we still have employees
with DeptId 1, so if the IT department is deleted from the Departments table, we would have no clue
to which department both Mark and Mary belong to. So, the integrity of the data will be lost, hence
the delete statement failed with an error. This is the default behaviour. However we can change it if
we want to. Now, what is our requirement? Well, when a row from Departments table is deleted,
all the related rows from the Employees table must also be deleted. We can very
easily achieve this by enforcing cascading deletes. The following are the steps. First,
drop the existing foreign key constraint and here is the command for that. Alter table table name,
in our case the foreign constraint is in the Employees table, so the table name is Employees,
drop constraint and the constraint name. To get the constraint name, expand Employees table,
go to keys and here is the foreign key constraint name. So, let's copy that and paste it right here.
Command completed successfully. Next, we need to add this foreign key constraint back but with
cascading deletes ON, and here is the command for that. Alter table Employees add constraint,
the name of the constraint and we want to add a foreign key constraint and it is the DeptId
column in our Employees table which is the foreign key referencing Id column from the Departments
table and here is the important bit, we are adding this foreign key constraint with cascading deletes
turned on. So, let's execute this command. Command completed and at this point, if we refresh this
keys folder, notice, we can see the foreign key constraint that we just added. Now, let's select
the data that we have in both these two tables and let's try to delete this IT department row
from the Departments table and see what's going to happen to Mark and Mary records. So, let's execute
this delete query. Command completed. Let's select the data again. Notice, IT department row is gone
from the Department's table and the respective employees are also gone from the Employees table.
So, here's the important bit to take away, with foreign key constraint cascade deletes in place,
when we delete a row from the Departments table, all the related rows from the Employees table
are also automatically deleted. Now, I have another question. What if we have the same
foreign key in multiple tables? In this example in both the tables that is Teachers and Students,
GenderId is a foreign key referencing Id column from the Gender table. Well, same idea,
with foreign key cascading deletes on, when a row from the Gender table is deleted, all the related
rows from both the tables that is Teachers and Students are also deleted automatically. Here
is the sql script that creates all the three tables - Genders, Teachers and Students. It
also populates them with test data. I'll have the script available in the description of this video
and here's the important bit, notice GenderId column in both the tables - Teachers and Students
is a foreign key referencing Id column from the Gender table and that too with cascading deletes
on. So, now let's see what's going to happen when we try to delete this row, "Male" gender row,
from the Gender table? All the male teachers that is Mark and John from Teachers table and David and
Ron from the Students table must also be deleted. So, let's execute this delete query and see what's
going to happen. Query executed successfully. So, let's select the data again. There we go.
All the related rows are also deleted as expected. Now I have another question, what if we don't
want to have foreign key constraint or we do not want to turn on cascading deletes? Well,
in that case you can use a sql query like this one to do the deletes yourself. Notice,
first I'm creating a variable here to hold the Id of the gender that we want to delete. First
we are deleting the related rows from the two child tables - Teachers and Students
and finally deleting from the parent table. If all the three delete statements complete successfully
then we are committing the transaction otherwise rolling it back, because we want either all
the three delete statements to complete successfully or none of them should happen.
This is the reason I am using a sql transaction. Behind the scenes, I have also dropped the foreign
key constraint from both these tables - Students and Teachers. Notice, in the "keys" folder,
we only have primary key constraint, we don't have the foreign key constraint. Now, let's select the
data that we have in these three tables. Now, when we delete this row with Id = 2, that is "Female"
from the Gender table, all the female teachers and female students must also be deleted. Let's see
if our script does that. So, let's select this entire script and execute it. Query completed
successfully. Let's select the data again. There we go, everything deleted as expected. Now, if
you're writing a query to do the deletes yourself then it's very important you first delete the rows
from the child tables and then from the parent table, why? Well, let's see that in action. In the
interest of time, behind the scenes, what I have done is, recreated these three tables - Gender,
Teachers and Students. GenderId is added as a foreign key in both the tables - Teachers
and Students, but I don't have cascading deletes on because we're going to write our own script to
do the deletes ourselves. So, if we take a look at the script here, notice I changed the order,
first I'm trying to delete from the parent table and then deleting from the child tables. Let's see
what's going to happen and if you take a look at the data in these three tables, I've also inserted
that test data and we are trying to delete this row with Id = 2 and we are doing that first from
the parent table that is Gender table. Let's see what's going to happen when I execute this query.
We get the same foreign key constraint error, that's why it's very important to delete from the
child tables first and then from the parent table. That's it in this video. Thank you for listening.