MySQL 中的外键

it2022-05-07 2

表和表之间可存在引用关系，这在抽象数据到表时，是很常见的。这种联系是通过在表中创建外键（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 为从表。

外键关联表的同步操作

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

建立外键时，可指定 ON UPDATE <action> 和 ON DELETE <action> 子语句来指定发生 UPDATE 和 DELETE 操作时，外键关联表中数据该如何处理。MySQL 中支持五种处理（action）：

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

添加外键

创建外键的语法 [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;

所以这个关系里有两个父表 customer 和 product，一个从表 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)

专利

最新回复(0)

MySQL 中的外键

外键关联表的同步操作

添加外键

删除外键

相关资源

专利