MySQL：外键约束

1 什么是外键 

• 　　外键指的是在从表中，与主表的主键对应的那个字段
•        比如员工表的 dept_id,就是外键
•        使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

 

• 多表关系中的主表和从表
  • 主表: 主键id所在的表, 约束别人的表 

  •        从表: 外键所在的表, 被约束的表

 

 

2 创建外键约束 

1.语法格式:

　　<1>新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 

　　<2>已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

 

2.代码示例

　　<1>重新创建employee表, 添加外键约束

-- 先删除 employee表 
DROP TABLE employee;

-- 重新创建 employee表,添加外键约束 
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT, 
    dept_id INT,
    -- 添加外键约束
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

　　<2> 插入数据

-- 正常添加数据 (从表外键 对应主表主键)
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 
-- 插入一条有问题的数据 (部门id不存在) 
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3); 

 

添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性

 

 

3 删除外键约束 

语法格式

alter table 从表 drop foreign key 外键约束名称

代码示例

<1>　　删除外键约束

-- 删除employee 表中的外键约束,外键约束名 
emp_dept_fk ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

<2>　　再将外键添加回来

语法格式

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

-- 可以省略外键名称, 系统会自动生成一个 
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

 

4 外键约束的注意事项

<1> 从表外键类型，必须与主表主键类型一致，否则创建失败.

 

 <2>  添加数据时： 应该先添加主表中的数据.

-- 添加一个新的部门 （市场部）
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');
-- 添加一个属于新部门（市场部）的员工 
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);

<3>  删除数据时：应该先删除从表中的数据

-- 删除数据时 应该先删除从表中的数据 
-- 报错 Cannot delete or update a parent row: a foreign key constraint fails 
-- 报错原因 不能删除主表的这条数据,因为在从表中有对这条数据的引用 
DELETE FROM department WHERE id = 3;

-- 先删除从表的关联数据 
DELETE FROM employee WHERE dept_id = 3;

-- 再删除主表的数据 
DELETE FROM department WHERE id = 3;

可以把主表和从表的关系想象为：

　　主表：地基

　　从表：地基上的建筑