MySQL外键约束(FOREIGN KEY)创建、添加与删除详解

理解外键约束的作用与规则

外键是表中的一个特殊字段，通常与主键配合使用。它的主要作用是：

• 建立连接：在主表与从表之间建立逻辑关联。
• 约束数据：确保从表中对应字段的值必须在主表的主键中存在，或为空值，从而保证数据的一致性和完整性。

在使用外键时，需遵守以下核心规则：

1. 主表必须已存在，且已定义主键。
2. 外键列的数据类型必须与主表主键列的数据类型完全一致。
3. 外键列的数目必须与主表引用的主键列数目相同。
4. 外键可以为 NULL，但若不为空，其值必须是主表中存在的有效主键值。

第1步：在创建表时设置外键约束

当新建从表时，可以直接在 CREATE TABLE 语句中定义外键。这是最常用且推荐的做法，因为它能在表结构形成之初就确立关系。

操作步骤与示例

假设我们需要建立一个简单的“部门-员工”模型。首先创建一个部门主表 tb_dept1，然后创建员工从表 tb_emp6 并将 deptId 设为外键。

1. 创建主表：
   创建 tb_dept1，其中 id 为主键。

mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );

1. 创建从表并添加外键：
   使用 CONSTRAINT 关键字为外键命名，并使用 FOREIGN KEY 指定关联。

mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );

执行成功后，tb_emp6 中的 deptId 列将受到限制，无法插入未在 tb_dept1.id 中存在的部门编号。

注意：如果数据类型不匹配（如一个是 INT 另一个是 VARCHAR），系统会抛出 “ERROR 1005(HY000): Can't create table” 错误。

第2步：在修改表时添加外键约束

如果表已经存在但未设置外键，可以通过 ALTER TABLE 语句后期添加。此操作有严格前提：从表中外键列的所有非空数据，必须都能在从主表中找到对应的值，否则操作会失败。

操作步骤与示例

以已有的员工表 tb_emp2 为例，将其 deptId 字段关联到 tb_dept1 的主键 id。

1. 执行 ALTER 命令：

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);

1. 验证结果：
   使用 SHOW CREATE TABLE tb_emp2\G 查看表结构，确认是否包含 CONSTRAINT ... FOREIGN KEY 语句以及相应的索引 KEY `fk_tb_dept1` (`deptId`)。

成功添加后，该表即具备了外键约束能力。

第3步：删除外键约束

当不再需要表间的强制关联，或者需要重构表结构时，必须删除外键约束。删除前建议备份相关数据，以防后续误操作导致数据丢失。

操作步骤与示例

1. 获取外键名称：
   若不清楚外键的具体约束名，可通过 SHOW CREATE TABLE 表名 查看，或者查询信息模式。在上面的例子中，名为 fk_tb_dept1。
2. 执行删除命令：

mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;

1. 验证删除效果：
   再次使用 SHOW CREATE TABLE tb_emp2\G，你会发现 Create Table 输出中已不包含 CONSTRAINT ... FOREIGN KEY 部分，说明外键已成功移除。

删除仅解除引用完整性检查，不会删除外键列本身的数据。如需删除该列，需额外执行 DROP COLUMN。

总结

合理使用外键约束能有效防止脏数据的产生，维持数据库逻辑的一致性。在实际开发中，创建表时定义是最稳妥的方式；而对于历史遗留表，需谨慎评估数据现状后再执行 ALTER TABLE 添加或删除约束。以上就是MySQL外键约束(FOREIGN KEY)创建、添加与删除的详细内容，更多关于MySQL数据库设计优化的资料请关注本站其它相关文章！