3.6.6 使用外键

在 MySQL 中, InnoDB 表支持检查外键约束. 参阅 Chapter 15, InnoDB 存储引擎, 和 Section 1.8.2.3, “外键差异”.

外键约束不仅仅用于连接两个表. 除了 InnoDB 存储引擎, 在定义列时使用 REFERENCES tbl_name(col_name) 子句, 该句没有实际的效果, 只是作为一个备忘录或者注释告诉你当前定义的列是指的另外一张表的列. 使用这种语法时, 认识到这一点非常重要:

  • MySQL 不执行任何检查来确保 col_name 确实存在于 tbl_name (甚至 tbl_name 本身是否存在).

  • MySQL 不会对 tbl_name 执行任何类型的操作, 例如删除行以响应对你定义的表中的行的响应; 换句话讲, 这个语法不会对 ON DELETE or ON UPDATE 行为产生任何影响. (虽然你可以将 ON DELETE 或者 ON UPDATE 子句作为 REFERENCES 子句的一部分, 但它被忽略.)

  • 该语法创建一个列; 它不会创建任何索引或者键.

你可以创建列作为连接列, 如下所示:

  1. CREATE TABLE person (
  2.     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     name CHAR(60) NOT NULL,
  4.     PRIMARY KEY (id)
  5. );
  7. CREATE TABLE shirt (
  8.     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  9.     style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
  10.     color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
  11.     owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
  12.     PRIMARY KEY (id)
  13. );
  15. INSERT INTO person VALUES (NULL, 'Antonio Paz');
  17. SELECT @last := LAST_INSERT_ID();
  19. INSERT INTO shirt VALUES
  20. (NULL, 'polo', 'blue', @last),
  21. (NULL, 'dress', 'white', @last),
  22. (NULL, 't-shirt', 'blue', @last);
  24. INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
  26. SELECT @last := LAST_INSERT_ID();
  28. INSERT INTO shirt VALUES
  29. (NULL, 'dress', 'orange', @last),
  30. (NULL, 'polo', 'red', @last),
  31. (NULL, 'dress', 'blue', @last),
  32. (NULL, 't-shirt', 'white', @last);
  34. SELECT * FROM person;
  35. +----+---------------------+
  36. | id | name                |
  37. +----+---------------------+
  38. |  1 | Antonio Paz         |
  39. |  2 | Lilliana Angelovska |
  40. +----+---------------------+
  42. SELECT * FROM shirt;
  43. +----+---------+--------+-------+
  44. | id | style   | color  | owner |
  45. +----+---------+--------+-------+
  46. |  1 | polo    | blue   |     1 |
  47. |  2 | dress   | white  |     1 |
  48. |  3 | t-shirt | blue   |     1 |
  49. |  4 | dress   | orange |     2 |
  50. |  5 | polo    | red    |     2 |
  51. |  6 | dress   | blue   |     2 |
  52. |  7 | t-shirt | white  |     2 |
  53. +----+---------+--------+-------+
  56. SELECT s.* FROM person p INNER JOIN shirt s
  57.    ON s.owner = p.id
  58.  WHERE p.name LIKE 'Lilliana%'
  59.    AND s.color <> 'white';
  61. +----+-------+--------+-------+
  62. | id | style | color  | owner |
  63. +----+-------+--------+-------+
  64. |  4 | dress | orange |     2 |
  65. |  5 | polo  | red    |     2 |
  66. |  6 | dress | blue   |     2 |
  67. +----+-------+--------+-------+

当以这种方式使用时, REFERENCES 子句不会显示在 SHOW CREATE TABLE or DESCRIBE 的输出中:

  1. SHOW CREATE TABLE shirt\G
  2. *************************** 1. row ***************************
  3. Table: shirt
  4. Create Table: CREATE TABLE `shirt` (
  5. `id` smallint(5) unsigned NOT NULL auto_increment,
  6. `style` enum('t-shirt','polo','dress') NOT NULL,
  7. `color` enum('red','blue','orange','white','black') NOT NULL,
  8. `owner` smallint(5) unsigned NOT NULL,
  9. PRIMARY KEY  (`id`)
  10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

按这种方式使用 REFERENCES 作为注释或者 “提示” 创建列适用于 MyISAM 表.