In MySQL 3.23.44 and up, InnoDB
tables
support checking of foreign key constraints. See
Section 13.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
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:
tbl_name
(col_name
)
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.
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 INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' 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.
User Comments
Add your own comment.