Learning SQL - Design Tips - Maintain Referential Integrity.

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Maintain Relational Integrity?

In a previous lesson we covered joining tables. We have also discussed how we could create a history table by using a trigger to insert a new record into our history table whenever changes were made to our main tables.

Now we are going to take a look at how we can use triggers to delete related records when a 'master' record is deleted. If we don't delete related records then we may end up with a database full of poor little orphans!

We will also look at how we can use triggers to prevent deletion of 'master' records where 'child' records exist.

Here's our Data Model again, showing the relationship between Parents and Children.

Data Mdoel 1

In some cases we may want to delete any related child records when the parent record is deleted. So we'll deal with that first.

Zap the Orphans!

Here is the code we need to create a trigger to delete any 'child' records when we delete the 'master' record.

create trigger QiSQL_delete_children
after delete on QiSQL_Parents
begin
delete from
QISQL_children where parent_id = old.id;
end;

Now we can delete Susan Brown, our only parent with children and see what happens:

delete from qisql_parents where id = 4;

Have a look in the QiSQL_Children table ( you know how to do it by now!) to confirm that all Susan's children have been deleted. You can also check the QiSQL_Changes_History table that we set up in the Basic Triggers section to make sure that the History table has been updated with the four children that should have been deleted.

Stop! No Deletions Allowed!

A very nice use for triggers is to prevent 'master' records from being deleted where 'child' records exist. First we need to do a bit of housekeeping to re-insert some records as we have just deleted our only Parent that had children and all her children!

insert into QiSQL_Parents ( first_name , last_name ) values ( 'Susan' , 'Brown' );
insert into QiSQL_Children ( parent_id , first_name, last_name ) values ( 4 , 'Peggy' , 'Brown' );
insert into QiSQL_Children ( parent_id , first_name, last_name ) values ( 4 , 'Lucy' , 'Brown' );
insert into QiSQL_Children ( parent_id , first_name, last_name ) values ( 4 , 'Harry' , 'Brown' );
insert into QiSQL_Children ( parent_id , first_name, last_name ) values ( 4 , 'Quentin' , 'Brown' );

You can use the view qisql_v_parents_children we created in the Outer Joins section to check that all our Parents and children are back.

Now let's create a trigger that will prevent 'master' records in QiSQL_Parents being deleted if 'child' records in QiSQL_Children exist:

create trigger QiSQL_no_delete_Parents
before delete on QiSQL_Parents
for each row begin
select raise ( ABORT,'Parent can not be deleted - Children exist!')
where exists ( select 1 from QiSQL_children where parent_id = old.id );
end;

Oh no! Another new concept has been introduced here. The where exists (...) part of the code is a sub query, these will be discussed later in (surprisingly) the Sub Queries section.

Now when we try and delete Susan Brown ( our only Parent with Children):

delete from QiSQL_Parents where id = 4;

We get an error from SQLite - Error Code 19, 'Parent can not be deleted - Children exist!'

As Mr Burns would say - Excellent!

Children Need Mummies (or Daddies)!

Children Need Mummies (or Daddies)!

To stop 'child' records being created in QiSQL_Children with no valid 'master' or 'parent' record in QiSQL_Parents we can create another trigger:

create trigger QiSQL_no_orphans
before insert on QiSQL_Children
for each row begin
select raise ( ABORT,'Child can not be created - Parent does not exist!')
where not exists ( select 1 from QiSQL_parents where id = new.parent_id );
end;

Now let's try and insert a new Child record with a nonexistent Parent ID:

insert into QiSQL_Children ( parent_id , first_name, last_name ) values ( 99 , 'Gregor' , 'Smirnoff' );

Did it work? Of course not, our trigger has prevented the insertion of this record.