On This Page
I think it was in 2000 when I said something like "To prevent orphans you
must kill the children before killing the parents." I was with friends at
Sushi Nabe (an excellent Sushi restaurant in Chattanooga), and we received
some unusual looks from nearby tables. We weren't talking about biological
children, but about data. The topic was database design and we were explaining
to Keith (my friend) the concept of referential integrity. The term "parent"
is applied to a record in a independent table, while the dependent records
for that parent record are called "children." In the relationship "One dog
can learn zero or more skills" we're saying that the DOG record is the parent
record, and it can have zero or more child records in the SKILL table.
Database programmers often write code to ensure database integrity.
One situation they want to avoid is orphaned records. These are child
records with linking fields (foreign keys) that point to non-existent
parent records. It's important to delete a parent's child records
first, before deleting that parent record. In a complex database, a
parent record could have children in many different tables, so it's easy
to end up with orphaned records if you have a sloppy programmer.
Take our database scenario and look at the table in it's third normal form
and see if you can answer the following question:
"What would be required to delete a trick? A dog?"
Some joker in the office added a new trick (#13) called "Pant." We don't
want to track a dog's skill level at panting, so the boss wants all traces of
trick #13 removed from the database. First we delete all occurrences of the
trick from the child records (i.e., the SKILL table in our scenario). If we
were using SQL it would look like this: DELETE FROM skill WHERE trick = 13;
Now that the children are gone, we can safely delete the parent record (i.e.,
the record in the TRICK table). In SQL it would look like this: DELETE FROM
trick WHERE trick = 13;
After that was done we learned that a new dog, Rover, was entered into our database but
never actually enrolled. To complicate matters, an idiot of an employee
was confused and entered data from several different registrations. Consequently
Rover's record is trash, and it's useless even for marketing purposes. Therefore,
we first delete any SKILL records for Rover (he's dog #45) before deleting
dog record #45. In SQL that would be two commands:
DELETE FROM skill WHERE dog = 45;
DELETE FROM dog WHERE dog = 45;
Top of page
The deletion of records often requires an exclusive lock on those records.
Record locking is a database operation that prevents other users from accessing
a record while it is locked. This prevents two people from editing the same
record simultaneously (either somebody's changes will be lost, or the data will
be mixed!). Different database products have different features and methods for
locking, but I wanted you to know about them. At least you now know to go and
look for them and figure how and what is available.
Now let's think about a record locking example. What tables or records need
to be locked on the network during modification or deletion of a dog? A elegant
solution is to only lock the dog record, and apply inherited locking to all
child records. Your program would be written so that an attempt to delete a
child record would first lock the parent. The same would apply to record changes:
any modifications to a child record require that you have a lock on its parent.
Top of page
Normally, you don't want database integrity to be the responsibility of your
end users. Imagine the horrified looks on their faces as you insist that they
must "prevent orphans by killing the children before killing the parents!"
And do you want to teach SQL to every user? I'm not talking about the enormous
workload the training would require — I'm talking about the dangers of
nuclear-grade weapons in inexperienced hands. An entire database can be
devastated by an incompetent or disgruntled employee in a matter of minutes.
For your sake and theirs, database access is handled through pre-written programs.
The programs control who can do what, and hide the complexity of the programming commands.
Only developers, system administrators, and select power-users would be given access
to the database via SQL commands (and even then a database administrator might only
grant query access — inserts, updates, and deletions would be disabled).
There are some data integrity rules that should be universally enforced throughout
an application. Some of these rules can be enforced by the database itself.
For example, an employee's name is normally a required field in an employee
table! Most databases let you set a "Not Null" constraint so that a record
can NEVER be added or changed so that the name is missing. Another common
constraint is the Unique Constraint. The unique constraint is frequently
used on the linking fields (i.e., primary keys) of a table.
Then there are constraints that are much more complex and that are not
built into the database. You must code these constraints in your program, and perform
data validation before data is added, deleted, or stored.
For example, you might have a business rule that states:
Employees cannot take more than a week of vacation until they've
worked with the company for more than two years. All employees,
regardless of seniority, cannot take more than two contiguous weeks
of vacation. Also, anyone taking more than four weeks of vacation
in a calendar year will be disciplined.
Enforcing that business rule is going to take quite a bit of programming,
and a single SQL statement isn't going to do it! Once you've programmed a
validation routine, you've got to implement it somewhere. You could store
it in an application, but there is another location available. Many databases
allow you to store code in the database itself! If a rule or program
universally applies to a table or field, why not store it there? Programs
that are stored in a database are called "Stored Procedures" or sometimes
"Stored Statements." A special type of stored procedure is a "Trigger"
which is what we'll discuss next.
Top of page
Triggers are stored procedures that are automatically run by the database
whenever certain "trigger" events occur. These events are normally a record
INSERT, UPDATE, or DELETE. For example, you could create a DELETE trigger
for the DOG table that automatically deletes all child records of that dog in
the SKILL table. Since the trigger is stored in the database it doesn't
matter what application or program accesses your database — you can
enforce data integrity through your trigger! Stored procedures and
triggers give you a centralized location for core logic, prevent the
circumventing of integrity rules, and are integrated with the data they protect.
Top of page
Copyright © 1995 Craig Edward Given