Sunday, July 27, 2008

Different Foreign Keys for Different Tables

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 Table of Contents, as well as a summary of 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 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.

Next Essay: Javascript as a Foreign Language

7 comments:

Dipan said...

Meja Makan Balero
Sofa Ganesa
Gazebo Sirap
Meja Makan Klasik Set
Bangku Gereja Minimalis

Regina Hilary said...

This is the best application on the drop today. There access to enjoy great moments of relaxation . Great! Thanks for sharing the information.Summon creatures to fight enemy units and demolish the opposing castle. Your castle is equipped with a crossbow, which you can use to shoot enemies . Make sure you upgrade skills to increase your chances of winning battles.:
age of war 2 | age of war 5 | age of war 6 | age of war 4


The goal of Age of War is to survive longer than the computer and to outlast him you’ll need to train the right troops while balancing your offence and defence in this high paced, quick thinking flash game . Train troops of you own to combat the computers. unfair mario
age of war 3 | age of war | unfair mario 2 | cubefield | happy wheels | tank trouble

The Marketer said...

I enjoyed your blog Thanks for sharing such an informative post. We are also providing the best services click on below links to visit our website.

digital marketing company in nagercoil
digital marketing services in nagercoil
digital marketing agency in nagercoil
best marketing services in nagercoil
SEO company in nagercoil
SEO services in nagercoil
social media marketing in nagercoil
social media company in nagercoil
PPC services in nagercoil
digital marketing company in velachery
digital marketing company in velachery
digital marketing services in velachery
digital marketing agency in velachery
SEO company in velachery
SEO services in velachery
social media marketing in velachery
social media company in velachery
PPC services in velachery
online advertisement services in velachery
online advertisement services in nagercoil

web design company in nagercoil
web development company in nagercoil
website design company in nagercoil
website development company in nagercoil
web designing company in nagercoil
website designing company in nagercoil
best web design company in nagercoil
web design company in velachery
web development company in velachery
website design company in velachery
website development company in velachery
web designing company in velachery
website designing company in velachery
best web design company in velachery

Thanks for Sharing - ( Groarz branding solutions )

tbs roku said...


A best data supplier, You are truly progressing nicely, it would be ideal if you keep it up.
tbs roku.

officecomsetup said...

Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative.

norton.com/setup
mcafee.com/activate
office.com/setup

Dell Error 2000-0321 said...

Amazing article, I am a big time fan of your site, keep up the nice work, and I will be a frequent visitor for a very long time Check out the way to fix Dell Error 2000-0321. Lean how you can fix it at your own or feel free to call our experts on our toll-free numbers or visit our website to know more!

jackwick said...

yahoo is the mail web service provider.yahoo email recovery and yahoo mail recovery is the process way of the yahoo account. that are many issue are create in yahoo account If you've change yahoo mail password, you can reset it to get back in to your Yahoo account but you are solve for this problem that you call to yahoo customer service number usa.
change yahoo mail password

Yahoo Password Reset

yahoo-customer-service-number-usa


Yahoo-email-recovery
Yahoo account recovery