如何给mysql表添加外键？mysql添加外键报错1215解决方法

在 MySQL 数据库中，外键（Foreign Key）用于建立和加强两个表数据之间的链接，确保参照完整性，这意味着外键列的值必须在主表的主键列中存在，或者为 NULL（如果允许）,以下是添加外键的详细操作指南。

前置条件与注意事项

在添加外键之前，必须确保满足以下技术约束,否则操作会失败：

1. 存储引擎：只有 InnoDB 存储引擎支持外键。MyISAM 等引擎不支持。
2. 字段类型匹配：外键列的数据类型、字符集和排序规则必须与主表主键列完全一致，如果主键是 INT UNSIGNED，外键也必须是 INT UNSIGNED。
3. 索引要求：外键列必须建立索引，如果该列尚未索引，MySQL 会自动创建；但如果主键列没有索引,则无法创建外键。
4. 数据一致性：在添加外键约束时，表中现有的数据必须符合参照完整性，如果外键列中存在在主表中没有对应值的记录,添加外键会失败。

创建表时直接定义外键

在创建新表时，可以直接在 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）。

外键动作详解

外键约束通常包含两个关键的动作定义,用于处理主表数据变更时的级联行为：

验证与删除外键

验证外键是否存在：

可以通过查询 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” 错误？

解答：
这个错误通常由以下原因引起：

1. 数据不一致：子表中存在外键列的值，但在主表中找不到对应的主键值。employees 表中有一条记录的 dept_id 为 999，但 departments 表中没有 dept_id 为 999 的记录，解决方法是先清理子表中的无效数据,或确保主表中有对应的数据。
2. 数据类型不匹配：外键列和主键列的数据类型、字符集或排序规则不完全一致，一个是 INT，另一个是 BIGINT；或者一个是 VARCHAR(50) CHARACTER SET utf8，另一个是 VARCHAR(50) CHARACTER SET latin1,解决方法是修改列定义使其完全匹配。
3. 存储引擎不支持：表使用的不是 InnoDB 引擎，解决方法是将表转换为 InnoDB：ALTER TABLE table_name ENGINE=InnoDB;。

问题 2：外键约束会影响数据库的性能吗？在什么情况下应该避免使用外键？

解答：
是的，外键约束会对性能产生一定影响,主要体现在：

1. 写入开销：每次插入、更新或删除数据时，数据库必须检查外键约束，这涉及额外的锁和索引查找操作，在高并发写入场景下,这可能成为瓶颈。
2. 锁竞争：外键检查可能导致更广泛的锁范围,增加死锁的风险。

应避免使用外键的情况：

1. 高并发写入系统：如大型互联网应用的热点数据表，为了追求极致写入性能，开发者常选择在应用层进行逻辑校验,而非依赖数据库外键。
2. 分库分表架构：在分布式数据库中，跨库的外键约束难以实现或性能极差,通常采用应用层关联或最终一致性方案。
3. 历史数据归档：对于只读或极少修改的历史数据表,外键的维护成本可能大于其带来的完整性收益。

在这些情况下，建议在应用代码层面进行数据完整性校验，或者使用触发器、存储过程等替代方案,但需注意应用层校验的复杂性和潜在的数据不一致风险。