A foreign key can be used to implement table design
patterns that span multiple tables. By choosing how
a foreign key handles a DELETE attempt on the parent
table, you can structure your table designs to
follow two standard patterns.
Welcome to the Database Programmer blog. This series
of essays is for anybody who wants to learn about
databases on their own terms. There is a complete
"http://database-programmer.blogspot.com/2007/12/database-skills-complete-contents.html"
>Table of Contents, as well as a summary of
"http://database-programmer.blogspot.com/2008/01/table-design-patterns.html"
>Table Design Patterns. There is a new essay in
this spot each Monday morning.
A Simple Example of Two Foreign Keys
Picture a basic shopping cart, with its two basic tables
of CART and CART_LINES (or ORDERS and ORDER_LINES if you
are more old-fashioned). The table CUSTOMERS is also
in there as a parent to CARTS. Our three tables look
something like this:
CUSTOMERS
|
|
/|\
CART Cart is child of customers
|
|
/|\
CART_LINES Lines is child of Cart
There are two foreign keys here. CART has a foreign key
to CUSTOMERS, and CART_LINES has a foreign key to CART,
but the two foreign keys should behave very differently.
Table Types and Table Design Patterns
In "http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html"
>A Sane Approach To Choosing Primary Keys we saw
that table design begins with identifying the basic
kinds of tables: Reference and Small Master Tables,
Large Master Tables, Transactions, and Cross-References.
Just as we picked different kinds of primary keys
for the different tables, so will we pick different
kinds of foreign keys between these tables.
Deleting a Customer
Imagine you have a customer who has made 10 orders
in 2 years. A system administrator, who is allowed
to basically do anything, goes into your admin
screens, looks up the customer, and clicks [DELETE].
What should happen?
The near-universal answer is that the user should
be denied the action. An error should come back that
says "That customer has orders, cannot delete."
We want it this way because we never want to delete
any parent row and "orphan" the child rows.
Database programmers know from long experience that
if you allow the DELETE, your queries will give incorrect
results, or you will work extremely hard with lots
of weird LEFT JOINS and UNIONS trying to
get them to come back correctly.
This is not an issue of "flexibility", where a more
robust system would allow the deletion. This is a
basic question of record-keeping. If the customer has
orders on file then the customer must be kept on file.
Enforcing this rule keeps code clean and simple, and
trying to avoid this rule in the name of "flexibility"
just makes heaps of work for everybody.
Going further, the administrator in question, who
supposedly can do anything, may not violate the rule.
An administrator is simply somebody who can do anything
that would not produce bad data. Administrators
should not be given the ability to violate the basic
structure of the data, they simply have full
rights to do anything within the structure of the
data.
The DELETE RESTRICT Foreign Key
The behavior we want here is called DELETE RESTRICT.
On most database servers this is the default
behavior for a foreign key. It means that you cannot
delete a parent table row if there are matching
rows in the child table.
The DELETE RESTRICT pattern is almost universally used
when the child table is a transaction table and the
parent table is a master table or reference table.
The syntax looks something like this:
-- Most database servers implement DELETE RESTRICT
-- by default, so this syntax:
Create table CART (
customer integer REFERENCES customers
,order integer.....
)
-- ...is the same as this explicit syntax:
Create table CART (
customer integer REFERENCES customers
ON DELETE RESTRICT
,order integer.....
)
Deleting An Order and DELETE CASCADE
Now let us say a staff member is on the phone with
a customer, enters an order, enters five lines,
and then the customers says "forget it" and the user
needs to delete the entire order from the CART.
In this case the user wants to go delete the order,
and he expects the computer to also delete the
lines. This makes perfect sense, why keep the
lines if we don't want the order?
It may seem strange that in the case of deleting
a customer it makes perfect sense to stop the user,
but when deleting an order it makes perfect sense
to delete the lines as well.
The difference is that an entry in the CART table
is a transaction entry. When a user deletes a
transaction they almost always want to automatically
delete all of the relevant rows from all child tables
as well. The two rules basically are:
- The user cannot delete a master entry that
has transactions.
- Deleting a transaction means deleting the
entire transaction.
NOTE: By "transaction" here I mean financial transaction
or other interaction between master elements. I do not
mean a database transaction.
The syntax for DELETE CASCADE looks something like this:
-- if the user deletes a row from CART,
-- do them the favor of deleting all of the
-- lines as well
Create table CART_LINES (
order integer REFERENCES CART
ON DELETE CASCADE
,order_line integer....
)
Conclusion: Different Tables Types, Different Foreign Key Types
I have said many times in these essays that the foreign key
is the only meaningful way to connect data in different
tables. This week we have seen that the kind of foreign
key you choose depends on what kind of tables you are
connecting together. Children of master tables generally
get DELETE RESTRICT, and children of transaction tables
generally get DELETE CASCADE.
"http://database-programmer.blogspot.com/2008/08/javascript-as-foreign-language.html"
>Next Essay: Javascript as a Foreign Language
