上一篇
如何给mysql表添加外键？mysql添加外键报错1215解决方法
在 MySQL 数据库中，外键（Foreign Key）用于建立和加强两个表数据之间的链接，确保参照完整性，这意味着外键列的值必须在主表的主键列中存在，或者为 NULL（如果允许）,以下是添加外键的详细操作指南。
前置条件与注意事项
在添加外键之前，必须确保满足以下技术约束,否则操作会失败：
- 存储引擎：只有 InnoDB 存储引擎支持外键。MyISAM 等引擎不支持。
- 字段类型匹配：外键列的数据类型、字符集和排序规则必须与主表主键列完全一致，如果主键是 INT UNSIGNED，外键也必须是 INT UNSIGNED。
- 索引要求：外键列必须建立索引，如果该列尚未索引，MySQL 会自动创建；但如果主键列没有索引,则无法创建外键。
- 数据一致性：在添加外键约束时，表中现有的数据必须符合参照完整性，如果外键列中存在在主表中没有对应值的记录,添加外键会失败。
创建表时直接定义外键
在创建新表时，可以直接在 CREATE TABLE 语句中定义外键，这是最清晰且推荐的方式,因为结构一目了然。
语法结构如下：
CREATE TABLE 子表名 ( 列1 数据类型, 列2 数据类型, ... CONSTRAINT 外键约束名 FOREIGN KEY (子表列名) REFERENCES 主表名 (主表列名) [ON DELETE 动作] [ON UPDATE 动作] );
示例：
假设有一个 departments 表（主表）和一个 employees 表（子表）。
-创建主表 CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) ); -创建子表并添加外键 CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE ON UPDATE CASCADE );
在此示例中，fk_emp_dept 是外键约束的名称。ON DELETE CASCADE 表示当主表中的部门被删除时，子表中对应的员工记录也会自动删除；ON UPDATE CASCADE 表示当主表中的部门 ID 更新时，子表中的对应 ID 也会同步更新。
在已存在的表中添加外键
如果表已经存在，可以使用 ALTER TABLE 语句来添加外键约束。
语法结构如下：
ALTER TABLE 子表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY (子表列名) REFERENCES 主表名 (主表列名) [ON DELETE 动作] [ON UPDATE 动作];
示例：
假设 employees 表已经存在，且 dept_id 列已存在,现在要添加外键约束：
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL ON UPDATE CASCADE;
这里使用了 ON DELETE SET NULL，意味着如果主表中的部门被删除，子表中对应员工的 dept_id 将变为 NULL（前提是 dept_id 列允许为 NULL）。
外键动作详解
外键约束通常包含两个关键的动作定义,用于处理主表数据变更时的级联行为：
| 动作类型 | 关键字 | 说明 |
|---|---|---|
| 删除动作 | RESTRICT | 默认值，禁止删除主表中被外键引用的记录。 |
| CASCADE | 删除主表记录时，自动删除子表中对应的所有记录。 | |
| SET NULL | 删除主表记录时，将子表中对应外键列设置为 NULL。 | |
| NO ACTION | 与 RESTRICT 类似，但在某些数据库实现中检查时机略有不同。 | |
| 更新动作 | RESTRICT | 默认值，禁止更新主表中被外键引用的主键值。 |
| CASCADE | 更新主表主键时，自动更新子表中对应的外键值。 | |
| SET NULL | 更新主表主键时，将子表中对应外键列设置为 NULL。 | |
| NO ACTION | 与 RESTRICT 类似。 |
验证与删除外键
验证外键是否存在：
可以通过查询 information_schema 数据库来查看当前表的外键约束信息：
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 = 'employees' AND REFERENCED_TABLE_NAME IS NOT NULL;
删除外键约束：
如果需要移除外键,使用以下语法：
ALTER TABLE 子表名 DROP FOREIGN KEY 外键约束名; ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;
注意：删除外键约束不会删除外键列本身，只会移除约束关系，如果还需要删除列，需额外执行 ALTER TABLE ... DROP COLUMN ...。
相关问题与解答
问题 1：为什么我在添加外键时遇到 “Cannot add or update a child row: a foreign key constraint fails” 错误？
解答：
这个错误通常由以下原因引起：
- 数据不一致：子表中存在外键列的值，但在主表中找不到对应的主键值。employees 表中有一条记录的 dept_id 为 999，但 departments 表中没有 dept_id 为 999 的记录，解决方法是先清理子表中的无效数据,或确保主表中有对应的数据。
- 数据类型不匹配：外键列和主键列的数据类型、字符集或排序规则不完全一致，一个是 INT，另一个是 BIGINT；或者一个是 VARCHAR(50) CHARACTER SET utf8，另一个是 VARCHAR(50) CHARACTER SET latin1,解决方法是修改列定义使其完全匹配。
- 存储引擎不支持：表使用的不是 InnoDB 引擎，解决方法是将表转换为 InnoDB：ALTER TABLE table_name ENGINE=InnoDB;。
问题 2：外键约束会影响数据库的性能吗？在什么情况下应该避免使用外键？
解答：
是的，外键约束会对性能产生一定影响,主要体现在：
- 写入开销：每次插入、更新或删除数据时，数据库必须检查外键约束，这涉及额外的锁和索引查找操作，在高并发写入场景下,这可能成为瓶颈。
- 锁竞争：外键检查可能导致更广泛的锁范围,增加死锁的风险。
应避免使用外键的情况：
- 高并发写入系统：如大型互联网应用的热点数据表，为了追求极致写入性能，开发者常选择在应用层进行逻辑校验,而非依赖数据库外键。
- 分库分表架构：在分布式数据库中，跨库的外键约束难以实现或性能极差,通常采用应用层关联或最终一致性方案。
- 历史数据归档：对于只读或极少修改的历史数据表,外键的维护成本可能大于其带来的完整性收益。
在这些情况下，建议在应用代码层面进行数据完整性校验，或者使用触发器、存储过程等替代方案,但需注意应用层校验的复杂性和潜在的数据不一致风险。
