MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
In MySQL, InnoDB
tables support checking of
foreign key constraints. See
Chapter 15, The InnoDB Storage Engine, and
Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.
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=utf8mb4
The use of REFERENCES
in this way as a
comment or “reminder” in a column definition works
with MyISAM
tables.