Mysql外键约束与关联表操作

最新推荐文章于 2025-04-22 15:14:08 发布

likepandas

最新推荐文章于 2025-04-22 15:14:08 发布

阅读量1.1k

点赞数 34

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_80950699/article/details/144224712

版权

一、外键约束

MySQL的外键约束是一种用于维护数据完整性和参照完整性的机制。它通过在一个表中定义一个或多个字段，引用另一个表的主键，确保数据之间的关系是有效的。外键约束可以防止插入无效的数据，并在数据表之间建立联系。以下是关于MySQL外键约束的详细信息：

外键的作用

确保数据完整性：外键约束确保引用的记录在主表中存在，防止孤立记录的产生。

维护参照完整性：通过外键，数据库可以自动管理表之间的关系，确保数据的一致性。

支持级联操作：可以设置级联删除或更新，当主表中的记录被删除或更新时，相关的子表记录也会相应地被删除或更新。

外键约束的类型：外键约束可以有不同的行为模式，如：

CASCADE：级联操作，删除或更新主表记录时，子表记录也会被删除或更新。

SET NULL：将子表中对应的外键字段设置为NULL。

NO ACTION：如果主表中有相关记录，禁止删除或更新

外键的特点

可以是单列或多列：外键可以由一个字段或多个字段组成，形成复合外键。

可以为空：外键字段可以允许NULL值，表示该记录不引用任何主表记录。

多个外键：一个表可以定义多个外键，建立与多个主表的关系。

外键约束的注意事项

性能影响：外键约束可能会影响插入、更新和删除操作的性能，因为数据库需要检查参照完整性。

设计考虑：在设计数据库时，应合理规划外键关系，以避免复杂的依赖关系和潜在的性能问题。

1. 创建客户表

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

customer_id 是主键，自增。
customer_name 是客户的名称。
email 是客户的电子邮件，必须唯一。

2创建订单表

通过FOREIGN KEY关键字定义外键

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

order_id 是主键，自增。
order_date 是订单日期。
customer_id 是外键，引用 customers 表的 customer_id。

3. 插入数据

INSERT INTO customers (customer_name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO customers (customer_name, email) VALUES ('Bob', 'bob@example.com');

| customer_id | customer_name | email |

| 1 | Alice | alice@example.com |

| 2 | Bob | bob@example.com |

向 orders 表中插入一些订单

INSERT INTO orders (order_date, customer_id) VALUES (NOW(), 1);  -- Alice's order
INSERT INTO orders (order_date, customer_id) VALUES (NOW(), 2);  -- Bob's order

4. 外键约束的效果

尝试删除 customers 表中的一条记录，比如删除 Alice：

DELETE FROM customers WHERE customer_id = 1;

由于我们在 orders 表中设置了 ON DELETE CASCADE，这将导致与 Alice 相关的所有订单记录也会被自动删除。因此，执行完上述删除后，orders 表的内容将变为：

| order_id | order_date | customer_id |

| 2 | 2023-10-01 12:05:00 | 2

5. 更新外键的效果

更新客户的 ID，例如将 Bob 的 ID 改为 3

UPDATE customers SET customer_id = 3 WHERE customer_id = 2;

二、关联表操作

1. 添加数据

添加客户和订单

首先，我们需要添加客户和与之相关的订单。注意，您必须先在 customers 表中插入客户，然后才能在 orders 表中插入订单。

-- 添加客户
INSERT INTO customers (customer_name, email) VALUES ('Charlie', 'charlie@example.com');

-- 获取刚插入客户的ID
SET @customer_id = LAST_INSERT_ID();

-- 添加订单，使用刚插入客户的ID
INSERT INTO orders (order_date, customer_id) VALUES (NOW(), @customer_id);

2. 更新数据

更新客户信息和订单信息

更新客户的电子邮件。
更新与该客户相关的订单日期。

-- 更新客户的电子邮件
UPDATE customers SET email = 'charlie.new@example.com' WHERE customer_id = @customer_id;

-- 更新该客户的订单日期
UPDATE orders SET order_date = NOW() WHERE customer_id = @customer_id;

3. 删除数据

删除客户和相关的订单

当您删除一个客户时，由于设置了外键约束（例如 ON DELETE CASCADE），与该客户相关的所有订单也会被自动删除。

-- 删除客户，相关的订单将自动删除
DELETE FROM customers WHERE customer_id = @customer_id;

三、关联表操作总结

添加数据：

确保先添加主表（customers）中的数据，然后再在子表（orders）中添加数据，使用主表的主键作为外键。

更新数据：

可以直接更新主表或子表中的数据。若更新主表中的主键，并且设置了 ON UPDATE CASCADE，子表中的外键也会自动更新。

删除数据：

删除主表中的记录时，若设置了 ON DELETE CASCADE，相关的子表记录会被自动删除。否则，若子表中有相关记录，删除操作将会失败。

完整示例：

-- 1. 添加客户
INSERT INTO customers (customer_name, email) VALUES ('David', 'david@example.com');
SET @customer_id = LAST_INSERT_ID();

-- 2. 添加订单
INSERT INTO orders (order_date, customer_id) VALUES (NOW(), @customer_id);

-- 3. 更新客户的电子邮件
UPDATE customers SET email = 'david.new@example.com' WHERE customer_id = @customer_id;

-- 4. 更新订单的日期
UPDATE orders SET order_date = NOW() WHERE customer_id = @customer_id;

-- 5. 删除客户（相关的订单将被自动删除）
DELETE FROM customers WHERE customer_id = @customer_id;