MySQL 中添加和删除外键约束

MySQL 中添加和删除外键约束

外键约束用于维护表之间的引用完整性。以下是 MySQL 中添加和删除外键约束的详细说明。

一、添加外键约束

基本语法

ALTER TABLE 子表名
ADD CONSTRAINT 约束名称
FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名)
[ON DELETE 引用动作]
[ON UPDATE 引用动作];

引用动作选项

• RESTRICT：拒绝删除/更新父表记录（默认）
• CASCADE：级联删除/更新子表记录
• SET NULL：将子表外键设为NULL
• NO ACTION：类似RESTRICT
• SET DEFAULT：设为默认值（InnoDB不支持）

示例

1. 基本外键添加

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

2. 带级联删除

ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE;

3. 带SET NULL选项

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE SET NULL;

4. 多列外键

ALTER TABLE order_details
ADD CONSTRAINT fk_product
FOREIGN KEY (product_id, product_version) 
REFERENCES products(id, version);

二、删除外键约束

基本语法

ALTER TABLE 表名
DROP FOREIGN KEY 约束名称;

示例

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

三、查看外键约束

1. 查看表结构

SHOW CREATE TABLE 表名;

2. 查询information_schema

SELECT 
    TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_SCHEMA = '数据库名'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

四、注意事项

1. 存储引擎：只有InnoDB支持外键约束
2. 数据类型：外键列和引用列必须类型兼容
3. 索引要求：
   • 子表的外键列会自动创建索引（如果不存在）
   • 父表的被引用列必须有主键或唯一约束
4. 性能影响：外键约束会带来一定的性能开销
5. 循环依赖：避免创建循环外键关系

五、完整示例

-- 创建父表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 创建子表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    dept_id INT,
    INDEX (dept_id)  -- 为外键列创建索引（可选，会自动创建）
);

-- 添加外键
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE RESTRICT;

-- 查看外键
SHOW CREATE TABLE employees;

-- 删除外键
ALTER TABLE employees
DROP FOREIGN KEY fk_emp_dept;

六、常见问题解决

问题1：无法添加外键

错误：Cannot add foreign key constraint

可能原因：

• 父表不存在或被引用列不是主键/唯一键
• 数据类型不匹配
• 存储引擎不是InnoDB

问题2：违反外键约束

错误：Cannot delete or update a parent row

解决方案：

1. 先处理子表记录
2. 使用级联操作
3. 临时禁用外键检查：

   SET FOREIGN_KEY_CHECKS = 0;
   -- 执行操作
   SET FOREIGN_KEY_CHECKS = 1;
   

通过合理使用外键约束，可以确保数据库的引用完整性，但需要权衡其对性能的影响。