Cascade Deletes

I’ve rarely seen cascade deletes used by SQL Server developers, but they can be handy in the right situations. If you are unaware of what cascade deletes are, they are a way of ensuring deletes don’t leave you with orphaned records. For instance, without cascade deletes, it’s up to the developer(s) to ensure that the corresponding phone records are deleted when a person is deleted from the database. Cascading deletes however, automatically delete the children (phone) when the parent (person) is deleted.

Below is an example of how to do them.

drop table tbPerson
drop table tbPhone

create table tbPerson (	PersonID int identity,
FirstName varchar(50) not null,
constraint PK_tbPerson primary key clustered(PersonID))

create table tbPhone (	PhoneID int identity,
PersonID int not null,
PhoneNum char(10) not null,
constraint PK_tbPhone primary key clustered (PhoneID),
constraint FK_tbPhone_tbPerson foreign key (PersonID) references tbPerson (PersonID)
on delete cascade
on update cascade
)

insert into tbPerson (FirstName) values ('Bob')
insert into tbPhone (PersonID, PhoneNum) values (1, '2177266291')

select * from tbPerson
select * from tbPhone

delete from tbPerson where FirstName = 'Bob'

select * from tbPerson
select * from tbPhone