MySQL –– FOREIGN KEYS
written by: admin
Date Written: 11/11/09
Last Updated: 4/22/11
NOTE: This is a continuation of
MySQL –– JOIN article
Intro
Using foreign keys in tables is a way to help restrict the data that is inserted into a database with user defined values. For example I can define a
table1 to have a column called
names be restricted to only the vales from
table2 column3. To elaborate further I can have the names Fred, Barney, and Wilma in
column3 of
table2. Now, when I try to insert values into the
names column I can enter whatever I want, but only the names Fred, Barney, and Wilma will be accepted. This helps to eliminate missing data from queries. In order to use Foreign keys
INNODB must be used.
CREATE TABLE notes (
id INT AUTO_INCREMENT,
addressee_id INT,
day DATE,
note TEXT,
PRIMARY KEY(id),
FOREIGN KEY(addressee_id)
REFERENCES people(id)
ON DELETE CASCADE
) ENGINE=INNODB;
In this table there is a
FOREIGN KEY, which means that this table is a child table. The
REFERENCES says what the parent table and column is that the
FOREIGN KEY relates to.
ON DELETE CASCADE is the action to take when the
REFERENCE KEY is altered in some way. In this case if
people.id is deleted then the corresponding row
notes.addressee_id is deleted as well. The
FOREIGN KEY is declared in the child table.
REFERENCES is the parent column that the FOREIGN KEY is matched against. For example, if the parent table deletes a row that is referenced by this child table then the child table
notes will delete the row that matches the one deleted by the parent row.
See the example below.
Example
Creating the tables
CREATE TABLE people (
id INT AUTO_INCREMENT,
name VARCHAR(128),
default_position_id INT,
PRIMARY KEY(id))
ENGINE=INNODB;
CREATE TABLE notes (
id INT AUTO_INCREMENT,
addressee_id INT,
day DATE,
note TEXT,
INDEX (addressee_id),
PRIMARY KEY(id),
FOREIGN KEY(addressee_id) REFERENCES people(id) ON DELETE CASCADE) ENGINE=INNODB;
Important: When creating these tables the foreign key must be indexed and
ENGINE=INNODB must be selected for
both the parent and child tables as demonstrated in the two tables above.
add data:
people notes
id name default_position_id id addressee_id day note
1 fred 22 1 1 2009–11–11 some text
2 barney 22 2 2 2009–11–11 some text e
3 wilma 22 3 3 2009–11–11 some textdd
4 john 2232 4 4 2009–11–11 some tex22tdd
5 michael 232
6 aaron 7
If you delete row 2 from
people then row 2 from
notes will be deleted as well giving you:
people notes
id name default_position_id id addressee_id day note
1 fred 22 1 1 2009–11–11 some text
3 wilma 22 3 3 2009–11–11 some textdd
4 john 2232 4 4 2009–11–11 some tex22tdd
5 michael 232
6 aaron 7
This also means that before you can insert row 2 back into notes row 2 in people will need to exist first otherwise you will get the error:
Cannot add or update a child row: a foreign key constraint fail.
One possible use for this is if I were to delete a name from the parent table then all records where the employee was present can also be removed as well.
Links
http://dev.mysql.com/doc/refman/5.0/en/join.html
http://dev.mysql.com/doc/refman/5.1/en/innodb–foreign–key–constraints.html
MySQL –– Normalized Tables
MySQL –– FOREIGN KEYS
MySQL –– JOIN
http://articles.techrepublic.com.com/5100–10878_11–6035435.htmlTAGS: mysql