mysql之表的外键约束

MySQL表的外键约束详细介绍及代码示例 外键约束是数据库中用于维护数据完整性和一致性的重要机制。它确保一个表中的数据与另一个表中的数据相关联，防止无效的数据引用。本文将详细介绍了外键约束的各个方面，并通过具体的代码示例进行演示。

1. 外键约束的基本概念

• 主键（Primary Key）：一个表中的一个或多个列，用于唯一标识表中的每一行数据。

• 外键（Foreign Key）：另一个表中的列，它引用了主键表中的主键列。 外键约束确保外键表中的外键值必须存在于主键表中的主键列中，或者为空。

---

2. 创建表时定义外键约束

假设我们有两个表：departments和employees。

-- 创建departments表
CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(100) NOT NULL
);
-- 创建employees表，并添加外键约束
CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(100) NOT NULL,department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

---

3. 级联操作

级联操作包括级联删除和级联更新，当主键表中的数据发生变化时，外键表中的数据会自动进行相应的变化。

3.1 级联删除

-- 创建customers表
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name VARCHAR(100) NOT NULL
);
-- 创建orders表，并设置级联删除
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE NOT NULL,FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

3.2 级联更新

-- 创建orders表，并设置级联更新
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE NOT NULL,FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE
);

---

4. 插入、更新和删除数据

-- 插入数据到customers表
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'John Doe');
INSERT INTO customers (customer_id, customer_name) VALUES (2, 'Jane Smith');
-- 插入数据到orders表
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1, '2023-04-01');
-- 更新customers表中的customer_id
UPDATE customers SET customer_id = 3 WHERE customer_id = 1;
-- 删除customers表中的记录，会级联删除orders表中的相关记录
DELETE FROM customers WHERE customer_id = 3;

---

5. 多列外键约束

-- 创建products表
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100) NOT NULL
);
-- 创建orders表
CREATE TABLE orders (order_id INT PRIMARY KEY,order_date DATE NOT NULL
);
-- 创建order_items表，并添加多列外键约束
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT NOT NULL,PRIMARY KEY (order_id, product_id),FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

---

6. 添加、修改和删除外键约束

6.1 添加外键约束

-- 添加外键约束
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);

6.2 修改外键约束

-- 删除现有外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_department;
-- 添加新的外键约束，带级联操作
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE ON UPDATE CASCADE;

6.3 删除外键约束

-- 删除外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_department;

---

7. 禁用和启用外键约束

-- 禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;
-- 启用外键约束
SET FOREIGN_KEY_CHECKS = 1;

注意：禁用外键约束可能会导致数据不一致，应谨慎使用。

8. 查课外键约束信息

-- 查看表的外键约束
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND REFERENCED_TABLE_NAME IS NOT NULL;

注意：将your_database_name和your_table_name替换为实际的数据库和表名称。

9. 外键约束的优缺点

优点：

• 保证数据的完整性和一致性。

• 防止无效的数据引用。

• 自动维护引用关系，通过级联删除和级联更新。 缺点：

• 可能会影响数据库的性能，尤其是在处理大量数据时。

• 可能会限制数据库的灵活性，使得数据库 schema 的更改变得更加复杂。

• 禁用外键约束可能会导致数据不一致。

---

10. 总结

外键约束是数据库中维护数据完整性的重要机制，通过确保表之间的引用关系有效，防止无效的数据引用。本文详细介绍了外键约束的定义、创建、级联操作、添加/修改/删除约束、禁用/启用约束以及其优缺点，并通过具体的代码示例进行了演示。在实际应用中，应根据业务需求谨慎使用外键约束，以平衡数据完整性和系统性能。