MySQL 中的外键

服务器

浏览数:219

2019-5-12

表和表之间可存在引用关系,这在抽象数据到表时,是很常见的。这种联系是通过在表中创建外键(foreign key)来实现的。

比如一个订单,可能关联用户表和产品表,以此来记录买了什么产品

约定两个概念:

父表:被引用的表。
从表:表中有相应的外键引用父表中的字段。

示例:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

这里 parent 为父表,child 为从表。

外键关联表的同步操作

当表和表之间建立起合适的关联后, INSERTUPDATE 操作会自动检查所插入的记录中指定的外键在相应表中是否存在;

建立外键时,可指定 ON UPDATE <action>ON DELETE <action> 子语句来指定发生 UPDATEDELETE 操作时,外键关联表中数据该如何处理。MySQL 中支持五种处理(action):

  • CASCADE:更新或删除父表记录时,自动更新或删除从表中匹配的记录。实际使用时注意不要在父表或从表中对同一列重复定义 ON UPDATE CASCADE。CASCADE 类型的操作不会激活触发器 (triggers)。
  • SET NULL:更新或删除父表中记录时,将从表中匹配的记录其外键设置为 NULL,前提时从表中该外键没有指定为 NOT NULL
  • RESTRICT:默认为该项。禁用父表中的更新或删除操作,这与缺省 ON DELETEON UPDATE 子语句效果一样。
  • NO ACTION:来自 SQL 标准中定义的一种操作,与上面 RESTRICT 等效。一些数据库支持延迟检查(deferred check), NO ACTION 便是这种可延迟检查的操作。在 MySQL 中,外键的检查是及时的,所以 NO ACTIONRESTRICT 完全等效。
  • SET DEFAULT: MySQL 能够解析识别该动作,但 InnoDBNDB 引擎不支持。

添加外键

创建外键的语法

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

文章开头提到的订单表示例:

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

所以这个关系里有两个父表 customerproduct,一个从表 product_order

对现有表添加外键可使用如下的语句:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

创建外键时可指定 [symbol],即给外键取一个名称,这样在其他操作时可以引用,比如删除外键时。

删除外键

同样是通过 ALTER TABLE 语句来完成。

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

删除外键时,如果该外键在创建时取了名称,名通过该名称来删除,如果没有,则需要先查询 MySQL 在创建该外键时自动生成的名称 fk_symbol 是什么。可通过 SHOW CREATE TABLE <table_name> 来完成查询。譬如:

mysql> SHOW CREATE TABLE dept_manager\G
*************************** 1. row ***************************
       Table: dept_manager
Create Table: CREATE TABLE `dept_manager` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) COLLATE utf8mb4_general_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

相关资源

作者:刘哇勇