3.6.6. Using Foreign Keys
In MySQL, InnoDB tables support checking of
foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”, and
Section 1.9.5.5, “Foreign Keys”.
A foreign key constraint is not required merely to join two
tables. For storage engines other than
InnoDB, it is possible when defining a column
to use a REFERENCES
tbl_name(col_name)
clause, which has no actual effect, and serves only as
a memo or comment to you that the column which you are currently
defining is intended to refer to a column in another
table. It is extremely important to realize when
using this syntax that:
MySQL does not perform any sort of CHECK
to make sure that col_name
actually exists in tbl_name (or
even that tbl_name itself
exists).
MySQL does not perform any sort of action on
tbl_name such as deleting rows in
response to actions taken on rows in the table which you are
defining; in other words, this syntax induces no ON
DELETE or ON UPDATE behavior
whatsoever. (Although you can write an ON
DELETE or ON UPDATE clause as
part of the REFERENCES clause, it is also
ignored.)
This syntax creates a column; it does
not create any sort of
index or key.
This syntax will cause an error if used in trying to define
an InnoDB table.
You can use a column so created as a join column, as shown here:
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
When used in this fashion, the REFERENCES
clause is not displayed in the output of SHOW CREATE
TABLE or DESCRIBE:
SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The use of REFERENCES in this way as a
comment or “reminder” in a column definition works
with both MyISAM and
BerkeleyDB tables.
