Primary and Foreign Keys in MySQL
In all relational databases, keys are used to establish relationships between different tables. Tables should (almost) always have a defined primary key because this ensures that each row in the table is unique.
Typically, a column with integers (often called id) is used, whose value is then automatically incremented by one for each new tuple (row) in the table. It is also practical to define foreign keys that refer to primary keys in other tables to maintain the integrity of the information. This article will demonstrate how this is done in practice.
When normalizing data, there will be several tables that are related to each other. To exemplify, we will again use artists and albums to make it concrete.
Before we begin, I would like to recommend two other related articles that this one builds upon:
- If you are unsure about what normalization means, you can read a brief introduction in the article A Little Basic Database Theory.
- If you want to read a short introduction to MySQL, which is used below, you can check out the article SQL in the MySQL Database Management System.
In our example below, all artists are related to one or more albums. Each album, however, is only related to one artist. So, in the album table, the attribute (column) artist is a foreign key to the artists table, using the terms mentioned above.
We will exemplify this below, but first, a little background information…
Keys in MySQL
There are different so-called storage types in MySQL, with the two most popular being MyISAM and InnoDB. To use foreign keys in MySQL, the tables must be of the InnoDB type. The differences between the various types will not be discussed here; we will simply note that the tables must be of the InnoDB type to utilize many of MySQL's more advanced features such as:
- Foreign keys
- Transactions
- Automated Crash Recovery
Since MySQL 4.1, InnoDB has also been the default type upon installation.
Our Tables and Data
To have some data to work with, we create the following two tables and insert a few rows into them. Note how we define a foreign key (as described above) for the album table. Typically, an integer is often used as a primary key in tables like artists, but to illustrate the concept, we will not do that and will use the artist's name as the primary key directly.
mysql> CREATE TABLE artists
-> (
-> artist VARCHAR(40),
-> www VARCHAR(256),
- >
-> PRIMARY KEY (artist)
-> ) TYPE=INNODB;
mysql> CREATE TABLE albums
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> artist VARCHAR(40),
-> title VARCHAR(40),
-> year DATE,
- >
-> PRIMARY KEY (id),
-> FOREIGN KEY (artist) REFERENCES artists(artist)
-> ON DELETE RESTRICT
-> ON UPDATE CASCADE
-> ) TYPE=INNODB;
Using the DESCRIBE command, we can see what the result looks like. In this case, the above code results in the following two tables:
mysql> DESCRIBE artists;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| artist | varchar(40) | NO | PRI | | |
| www | varchar(256) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)
mysql> DESCRIBE albums;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| artist | varchar(40) | YES | MUL | NULL | |
| title | varchar(40) | YES | | NULL | |
| year | date | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0,00 sec)
To have something to work with, we will also insert some data into the tables. This is done with a few standard INSERT statements. Note that we do not need to define any values for the id column since it is set as auto_increment.
mysql> INSERT INTO artists (artist, www) VALUES ("R.E.M.", "http://www.remhq.com");
mysql> INSERT INTO artists (artist, www) VALUES ("Live", "http://www.friendsoflive.com/");
mysql> INSERT INTO albums (title, artist, year) VALUES ("Out of Time", "R.E.M.", "1991-03-12");
mysql> INSERT INTO albums (title, artist, year) VALUES ("Reveal", "R.E.M.", "2001-05-07");
mysql> INSERT INTO albums (title, artist, year) VALUES ("Mental Jewelry", "Live", "1992-03-09");
And to see the result, we can use two simple SELECT statements:
mysql> SELECT * FROM artists;
+--------+-------------------------------+
| artist | www |
+--------+-------------------------------+
| Live | http://www.friendsoflive.com/ |
| R.E.M. | http://www.remhq.com |
+--------+-------------------------------+
2 rows in set (0,00 sec)
mysql> SELECT * FROM albums;
+----+--------+----------------+------------+
| id | artist | title | year |
+----+--------+----------------+------------+
| 1 | R.E.M. | Out of Time | 1991-03-12 |
| 2 | R.E.M. | Reveal | 2001-05-07 |
| 3 | Live | Mental Jewelry | 1992-03-09 |
+----+--------+----------------+------------+
3 rows in set (0,00 sec)
In Action
For foreign keys to be defined, several requirements must be met (see the full list in the MySQL documentation):
- Both tables must be InnoDB
- Both fields must have the same data type (varchar(40) in our case)
What happens when a tuple that is referenced by a foreign key is deleted or updated is defined when the table is created. Here are the three most common options, along with an example of what happens if we define ON DELETE for each option in the tables above.
CASCADE– If we delete the row with "Live" from artists, all rows that reference it will also be deleted. Row number 3 in the albums table will thus be automatically removed.SET NULL– If we delete the row with "Live" from artists, all rows that reference it will have their artist value set to NULL.RESTRICT– If we try to delete the row with "Live" from artists, we will receive an error message because there are still rows in the albums table that reference "Live". The operation will therefore not be executed.
In our case (with ON DELETE RESTRICT and ON UPDATE CASCADE), we will get the following result if we try to delete the row with "Live" in the artists table:
mysql> DELETE FROM artists WHERE artist="Live";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`artiklar_mysql/albums`,
CONSTRAINT `albums_ibfk_1` FOREIGN KEY (`artist`) REFERENCES `artists` (`artist`) ON UPDATE CASCADE)
… but if we instead update the value, the change will take effect:
mysql> UPDATE artists SET artist="REM" WHERE artist="R.E.M.";
mysql> SELECT * FROM artists;
+--------+-------------------------------+
| artist | www |
+--------+-------------------------------+
| Live | http://www.friendsoflive.com/ |
| REM | http://www.remhq.com |
+--------+-------------------------------+
2 rows in set (0,00 sec)
And we can see that the change has also taken effect in the table that we did not directly modify:
mysql> SELECT * FROM albums;
+----+--------+----------------+------------+
| id | artist | title | year |
+----+--------+----------------+------------+
| 1 | REM | Out of Time | 1991-03-12 |
| 2 | REM | Reveal | 2001-05-07 |
| 3 | Live | Mental Jewelry | 1992-03-09 |
+----+--------+----------------+------------+
3 rows in set (0,00 sec)
Summary
Using primary keys is something that should more or less be a given for every table you create when designing a database. Defining foreign keys is also very practical for maintenance and structure.
Just remember that ON DELETE CASCADE is an excellent way to keep your tables free from old data that is no longer used, but it is also a perfect way to delete a significant amount of information in mere milliseconds if something goes wrong…
This was a very brief crash course on different types of keys in MySQL. Besides keys, something called indexes is also very important and is primarily used on columns that are often included in WHERE and ORDER BY clauses, but that is a topic for another short article.
