Mysql Database Foreign Keys
Nov 07, 2008
by
0
What is a foreign key? A foreign key is a relationship made in a database to link one row of data to another row of data.
For example:
We have a person's name and birth date stored in a database table, we would then like to store N amount ( "N" being a number we feel like ) phone numbers for this person. In order to uniquely identify this person, we will use a system generated number for an ID. In another database table we would store the phone number, now we need a way to relate the two tables together. To do this, we would use a foreign key. In the phone number table, we would store the person id, so we will know what person this phone number is for.
In Mysql the most commonly used way to store data is using the MYISAM storage engine. This engine works pretty well for many things. One thing however that I like to do as a database administrator is to setup an enforced foreign key policy, such that if someone where to try and delete the person from the persons table without first deleting their phone number, the database would error out. The last thing you want in your database is a bunch of orphaned data, data that is taking up space but is not going to be used.
To enforce a foreign key relationship, I use the InnoDB storage engine with mysql. This allows me to inform the database on table creation, that I want to use said columns to link the tables together. This is great as you will never get any orphaned data in your database if you use an engine that can enforce the foreign key relationships; never again will an awry script create an unclean database relation.
As of current, the only engine for Mysql that will enforce and store these foreign key constraints is Innodb. On Mysql's website, it is stated that at a later date foreign key constraints will be implemented in the MyIsam engine as well.
Leave a comment
To leave a comment, please log in / sign up
RSS Feed
Facebook
Twitter
LinkedIn
YouTube