MySQL外键级联

外键介绍

在数据库中的某一张表中, 通过外键字段,可以把数据与另一张表管理起来.

我们举个例子来理解外键:

首先创建两张表,分别为CustomersOrdersCustomers为顾客信息表, 而Orders作为订单表:

创建Customers

1
2
3
4
5
6
CREATE TABLE `Customers` (
`cust_id` char(10) NOT NULL,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建Orders

1
2
3
4
5
6
7
8
CREATE TABLE `Orders` (
`order_id` int(11) NOT NULL,
`order_date` datetime NOT NULL,
`cust_id` char(10) NOT NULL,
KEY `cust_id` (`cust_id`),
CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

其中cust_id字段作为外键联结OrdersCustomers表, 外键绑定关系这里使用了ON DELETE CASCADE, ON UPDATE CASCADE, 即如果外键对应数据被删除或者更新时,将关联数据完全删除或者相应地更新.使用了插入数据后的两个表分别如下:

Customers顾客信息表

cust_id cust_name cust_address
1000000001 Tom 200 Maple Lane
1000000002 Jim 1 Sunny Place
1000000003 John 829 Riverside Drive
1000000004 Smith 4545 53rd Street

Orders订单表

order_id order_data cust_id
20005 2019-02-03 00:00:00 1000000001
20006 2019-02-07 00:00:00 1000000002
20007 2019-03-06 00:00:00 1000000003

外键约束

  1. 两个表必须是InnoDB表,MyISAM表暂时不支持外键.

  2. 外键列必须建立了索引, 目前MySQL5.6及以上的版本支持创建外键时自动创建索引, 所依无需显式的创建索引.

  3. 被外键引用的列必须是主键或唯一索引.

  4. 外键关系的两个表的列必须是数据类型相似, 即数据类型可以相互转换,比如inttinyint可以,而intchar则不可以.

根据约束, 假如我们插入Orders表一条数据,但其中的cust_id字段是Customers不存在的, MySQL会提示如下错误:

1
2
mysql> insert into Orders values(20006, '2019-09-03 00:00:00', '1000000006');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`))

外键级联

修改外键数据

我们将Customers表中的第一列数据进行修改:

1
2
3
mysql> UPDATE Customers SET cust_id=1000000005 WHERE cust_name='Tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

根据ON UPDATE CASCADE,Orders表中的对应的数据也被修改:

1
2
3
4
5
6
7
8
9
mysql> SELECT order_num, order_date, cust_id FROM Orders;
+-----------+---------------------+------------+
| order_num | order_date | cust_id |
+-----------+---------------------+------------+
| 20005 | 2019-02-03 00:00:00 | 1000000005 |
| 20006 | 2019-02-07 00:00:00 | 1000000002 |
| 20007 | 2019-03-06 00:00:00 | 1000000003 |
+-----------+---------------------+------------+
3 rows in set (0.00 sec)