MySQL添加外键约束|极客笔记

MySQL添加外键约束

1. 什么是外键约束

外键（Foreign Key）是关系型数据库中用于建立表与表之间关系的一种约束。它在一张表中关联另一张表的主键，以实现数据之间的引用和一致性约束。

在MySQL中，外键约束用于保证两个相关表之间的数据完整性。它可以确保引用表的记录在被关联表中存在，并且在修改或删除关联表的记录时，能够自动同步更新或禁止操作。

2. 添加外键约束的语法

在MySQL中，添加外键约束使用ALTER TABLE语句。下面是添加外键约束的一般语法：

ALTER TABLE 表名
ADD FOREIGN KEY (外键列) REFERENCES 主表名(主键列)
[ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT}];

其中，表名是要添加外键约束的表名，外键列是待关联的外键列名，主表名是关联的主表名，主键列是主表中用作主键的列名。

方括号内的内容是可选的，其中可以指定在更新或删除主表记录时要采取的操作。常见的操作包括：

CASCADE：级联操作，表示在主表上进行操作时，自动在关联表上执行相同操作。
SET NULL：设置为NULL值，表示在主表上进行操作时，将关联表中的外键列设置为NULL。
SET DEFAULT：设置为默认值，表示在主表上进行操作时，将关联表中的外键列设置为默认值。
RESTRICT：约束操作，表示在主表上进行操作时，如果有关联记录存在，则禁止操作。

3. 添加外键约束的注意事项

在添加外键约束之前，需要注意以下几点：

被关联的主表必须存在，并且主表中的主键列必须设置了主键约束。
外键列和主键列的数据类型必须一致，或者可以进行隐式转换。
外键列和主键列的字符集和排序规则必须一致。
外键约束只能应用于InnoDB存储引擎，其他存储引擎如MyISAM不支持外键约束。

如果不满足上述条件，可以先对表结构进行修改，以满足外键约束的要求。

4. 示例演示

为了更好地理解如何添加外键约束，下面给出一个示例。

4.1 创建主表和子表

首先，我们创建一个主表和一个子表来演示外键约束的使用。我们使用MySQL的命令行客户端进行操作。

主表：`orders`

执行以下SQL命令创建主表orders：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

子表：`order_items`

执行以下SQL命令创建子表order_items：

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

在子表order_items的order_id列上创建了外键约束，它引用了主表orders的主键order_id。

4.2 插入测试数据

接下来，我们向主表和子表插入一些测试数据，以验证外键约束的工作情况。

首先，插入主表orders的数据：

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, '2022-01-01', 100.00),
       (2, '2022-01-02', 200.00),
       (3, '2022-01-03', 300.00);

然后，插入子表order_items的数据：

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (1, 1, 2, 10.00),
       (1, 2, 1, 20.00),
       (2, 2, 3, 30.00),
       (3, 3, 1, 40.00),
       (3, 4, 2, 50.00);

4.3 测试外键约束

在添加外键约束之后，如果我们尝试删除主表中有关联记录的行，将会收到一个错误消息，因为MySQL会拒绝操作以保证数据一致性。

执行以下命令尝试删除一个有关联记录的行：

DELETE FROM orders WHERE order_id = 1;

输出如下：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`database`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

我们可以看到，MySQL拒绝了删除操作，并给出了相应的错误消息。这表明外键约束已经生效，防止了意外删除操作。

4.4 更新操作和级联操作

当主表的记录发生更新时，外键约束可以触发级联操作，自动在关联表上执行相应的操作。

例如，我们对主表orders中的一个记录进行更新，将total_amount增加100元：

UPDATE orders SET total_amount = total_amount + 100 WHERE order_id = 1;

然后，我们查询子表order_items的数据：

SELECT * FROM order_items WHERE order_id = 1;

输出如下：

+---------+----------+------------+----------+-------+
| item_id | order_id | product_id | quantity | price |
+---------+----------+------------+----------+-------+
|       1 |        1 |          1 |        2 | 10.00 |
|       2 |        1 |          2 |        1 | 20.00 |
+---------+----------+------------+----------+-------+

我们可以看到，子表order_items中的数据也随着主表的更新而更新，保持了数据的一致性。

4.5 删除外键约束

如果需要取消外键约束，可以使用ALTER TABLE语句进行删除。

执行以下SQL命令删除子表order_items上的外键约束：

ALTER TABLE order_items
DROP FOREIGN KEY order_items_ibfk_1;

此时，再尝试删除主表中有关联记录的行，将不再受到限制。

5. 总结

本文介绍了MySQL中添加外键约束的语法和注意事项。通过示例演示，我们了解了外键约束是如何确保关联表之间的数据完整性，并且在数据变更时自动执行相应操作的。使用外键约束可以有效地维护表之间的关系和数据一致性。

要添加外键约束，需使用ALTER TABLE语句，指定要添加外键约束的表名、外键列和关联的主表名和主键列。可以选择性地指定在更新或删除主表记录时要执行的操作，如级联操作、设置为NULL或默认值、约束操作等。

在添加外键约束之前，需要确保满足一些条件，如被关联的主表存在、外键列和主键列的数据类型一致等。不满足条件时，需对表结构进行修改。

示例中创建了一个主表orders和一个子表order_items来演示外键约束的使用。插入了测试数据并测试了外键约束的效果。尝试删除有关联记录的行时，MySQL拒绝了操作并输出错误消息。更新主表记录时，子表中的关联数据也随之更新，确保了数据的一致性。

MySQL添加外键约束