Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database.
Learn all about MySQL foreign keys in this white paper.
Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper.
Learn the importance of data validation in web applications when information is accepted from third parties, or even from internal users.
MySQL Foreign KeysHave 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.
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:
MySQL Foreign Key Definitions/Explanations
Self Table Foreign Keys in MySQLIn 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.
ConclusionsI 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
|Copyright © 2010-2015 Eliacom, Inc. All rights reserved.|