Other Helpful Stuff

MySQL/SQL Poisoning and SQL Insertion

Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database.

MySQL Foreign Keys

Learn all about MySQL foreign keys in this white paper.

MySQL Foreign Key Errors and Errno: 150

Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper.

MySQL/SQL Data Validation (with PHP)

Learn the importance of data validation in web applications when information is accepted from third parties, or even from internal users.

MySQL Foreign Keys

Have questions that weren't answered here? Feel we've left something out? Have any feedback? Let us know: Contact Us

What's a Foreign Key and Some Syntax/Nomenclature:

A foreign key (or foreign key constraint) is a constraint that requires a column or columns from a child table to match to a column or columns from a parent table. This parent/child relationship constrains all of the values in the child table to come from the parent table. It also can tell what the child table should do if its parent has been changed or removed.

An example of how this is used in databases is as follows. There are 50 states in the United States. You want to make a table with names of people and what state they live in. So you make a `users` table with two columns: `name`, and `state`. Then you make a `states` table with one column `id`. You would like to require that any field that makes it into the `state` column in the `users` table is a valid `id` as stored in the `states` table. To do this, you would add a foreign key to the `users` table. The syntax would look something like this in MySQL.

ALTER TABLE `users` ADD FOREIGN KEY (`state`) REFERENCES `states` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT

This will now require that the `state` in the `users` table must be one of the `id`s in the `states` table. For the more general syntax of a foreign key, the statement below shows the different parts of a foreign key creation statement for both altering tables and creating tables:

Altering MySQL Tables:


 Child Table 
ALTER TABLE `Table_name` ADD
 Optional Constraint Name Optional Index Name
  CONSTRAINT `constraint_name` FOREIGN KEY `optional_key_name`
 Child table columns Parent TableParent Columns
  (`column1`,`column2`) REFERENCES `reference_table` (`refcol1`,`refcol2`)
 Optional Option When Parent Is DeletedOptional Option When Parent Is Updated
 ON DELETE <DELETE OPTION>ON UPDATE <UPDATE OPTION>

Creating MySQL Tables:


 Child Table 
CREATE TABLE `Table_name` (`column1` definition1,`column2` definition2,...),
 Optional Constraint Name Optional Index Name
  CONSTRAINT `constraint_name` FOREIGN KEY `optional_key_name`
 Child table columns Parent TableParent Columns
  (`column1`,`column2`) REFERENCES `reference_table` (`refcol1`,`refcol2`)
 Optional Option When Parent Is DeletedOptional Option When Parent Is Updated
 ON DELETE <DELETE OPTION>ON UPDATE <UPDATE OPTION>

MySQL Foreign Key Definitions/Explanations

  1. Parent Table: A table that has some column or columns that will be the source of information for the child table.
  2. Parent Table Column(s): The column or columns that the source of the values in the child columns. Every item in the parent table does not necessarily have to exist in the child table. Using the above example, there are 50 states, but you are not guaranteed that your `users` table has someone from each state.
  3. Child Table: The table that has a column or columns that are required to match to some parent columns.
  4. Child Table Column(s): The column or columns that are required to match to the parent columns. Every child column must match to a value in the parent column. There is one exception that is typically allowed with foreign keys. The value in a child table can often be NULL, even though there is no parent value that is NULL. Using the above example, if you live in the United States, you have to live in some State, so every user (assuming all your users are in the States) would have to have a valid `state` matching to the `id` in the `states` table. You can even deal with international contingencies by setting all the users' states to NULL where they are not from the United States.
  5. Optional Constraint Name: A name (or more appropriately id) used by MySQL (or other database) to store the settings for this foreign key relationship. This has to be unique in a database, and if you leave it out, MySQL will just generate one automatically for you.
  6. Optional Index Name: When you create a foreign key, every time you try to insert or modify a record in the child table, or whenever you modify or delete a record in the parent table, MySQL has to figure out if that's ok. For instance, if you tried to delete California from the `states` table, and you had users from California, that would orphan those children. To check if there are users from California, MySQL has to scan the `users` table. In order to ensure that this doesn't take too long, both the child table columns and parent table columns are required to be indexed, so that this can be done more rapidly. MySQL will automatically create an index on the child table, and if you want to choose your own index name for the child table, you can do that here. Note that the parent table also needs an index, but there is no place to add that in here. If there isn't an index on the parent columns that can be used, you'll get an error (the infamous errno 150.
  7. ON DELETE OPTION: Because bad things like orphaning children (who wants that?) can happen when you delete rows from the parent table, MySQL allows you to tell it how to handle things when you try to delete a parent with children. The options are: RESTRICT, SET NULL, CASCADE, or NO ACTION (which in MySQL is the same as RESTRICT). RESTRICT means that it won't let you do it. If you try to delete a parent with existing kids, it stops you and throws and error (no orphans allowed). SET NULL means it allows the delete and sets the children to NULL (orphans I guess are allowed). CASCADE means that the delete cascades down to the child table, and children are killed too (no orphans allowed).
  8. ON UPDATE OPTION: This isn't as bad as killing parent rows, but if your parent changed their name, I'll bet you'd want to know. If a parent row is updated, then you can tell MySQL how to handle the name change by using the same options as above: RESTRICT, SET NULL, CASCADE, or NO ACTION (again same as restrict in MySQL). RESTRICT means parents don't get to change their name. It throws an error. SET NULL means that it sets the kids to null (orphans them) if their parents change their name (that's one way to get rid of you kids). CASCADE means that your kids change their name too (no orphans).

Self Table Foreign Keys in MySQL

In MySQL, you can actually make the same table both parent and child (weird). There are some situations where this is useful, but the rules are a little different for the ON UPDATE. Namely, you can't do ON UPDATE CASCADE. The reason for this is that MySQL could possibly get into an infinite loop in doing the update, so they just decided for now to disable ON UPDATE CASCADE self-table foreign keys.

Foreign Keys Are Amazing (when they work)

One issue with foreign keys is that they're hard to get started sometimes. Routinely, when you go to create a foreign key, MySQL will complain about something or other. It might say something like "A foreign key constraint fails," which means that there are some values in the child table that have no parent. Since this isn't allowed, you can create the foreign key at all. Or you can get a nebulous error that says something like "cannot create table... errno 150". It doesn't tell you why it can't, it just can't. For all things foreign key errors, see MySQL Foreign Key Errors for more information.

Conclusions

I like my parents, and I never want to get orphaned which is why I specifically had a foreign key installed with ON DELETE RESTRICT ON UPDATE RESTRICT (just in case they try to get a new identity to get away from me). Eliacom also has a MySQL GUI tool that lets you create, view and manage your foreign keys through a simple and straightforward system.

We hope you found this white paper useful. Please let us know if you have any questions you felt were not addressed in the white paper or if you have any feedback: Contact Us