作用

限定某个表的某个字段的引用完整性。
比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分。
image.png

关键字

FOREIGN KEY

主表和从表/父表和子表

  • 主表(父表):被引用的表,被参考的表

  • 从表(子表):引用别人的表,参考别人的表

例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表。

例如:学生表、课程表、选课表:选课表的学生和课程要分别参考学生表和课程表,学生表和课程表是主表,选课表是从表。

特点

  • 从表的外键列,必须引用/参考主表的主键或唯一约束的列

    1. 为什么?因为被依赖/被参考的值必须是唯一的
  • 在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。

  • 创建(CREATE)表时就指定外键约束的话,先创建主表,再创建从表

  • 删表时,先删从表(或先删除外键约束),再删除主表

  • 当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据

  • 在“从表”中指定外键约束,并且一个表可以建立多个外键约束

  • 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。

    1. 例如:都是表示部门编号,都是int类型。

(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。(根据外键查询效率很高)

(9)删除外键约束后,必须手动删除对应的索引

在创建表时添加外键约束

  1. create table 主表名称(
  2. 字段1 数据类型 primary key,
  3. 字段2 数据类型
  4. );
  5. create table 从表名称(
  6. 字段1 数据类型 primary key,
  7. 字段2 数据类型,
  8. [CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) references 主表名(被参考字段)
  9. );
  10. #(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致,逻辑意义也一样
  11. #(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样,也可以不一样
  12. -- FOREIGN KEY: 在表级指定子表中的列
  13. -- REFERENCES: 标示在父表中的列
  1. #①先创建主表
  2. CREATE TABLE dept1(
  3. dept_id INT,
  4. dept_name VARCHAR(15)
  5. );
  6. #②再创建从表
  7. CREATE TABLE emp1(
  8. emp_id INT PRIMARY KEY AUTO_INCREMENT,
  9. emp_name VARCHAR(15),
  10. department_id INT,
  11. #表级约束
  12. CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
  13. );
  14. # Cannot add foreign key constraint 报错!!!
  15. # 上述操作报错,因为主表中的dept_id上没有主键约束或唯一性约束。
  16. #③ 添加
  17. ALTER TABLE dept1
  18. ADD PRIMARY KEY (dept_id);
  19. DESC dept1;
  20. #④ 再创建从表
  21. CREATE TABLE emp1(
  22. emp_id INT PRIMARY KEY AUTO_INCREMENT,
  23. emp_name VARCHAR(15),
  24. department_id INT,
  25. #表级约束
  26. CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
  27. );
  28. DESC emp1;

说明: (1)主表dept1必须先创建成功,然后才能创建emp1表,指定外键成功。 (2)删除表时,先删除从表emp1,再删除主表dept1

外键效果

  1. #添加失败
  2. INSERT INTO emp1
  3. VALUES(1001,'Tom',10);
  4. INSERT INTO dept1
  5. VALUES(10,'IT');
  6. #在主表dept1中添加了10号部门以后,我们就可以在从表中添加10号部门的员工
  7. INSERT INTO emp1
  8. VALUES(1001,'Tom',10);
  9. #删除失败
  10. DELETE FROM dept1
  11. WHERE dept_id = 10;
  12. #更新失败
  13. UPDATE dept1
  14. SET dept_id = 20
  15. WHERE dept_id = 10;

在创建表之后添加外键约束

  1. CREATE TABLE dept2(
  2. dept_id INT PRIMARY KEY,
  3. dept_name VARCHAR(15)
  4. );
  5. CREATE TABLE emp2(
  6. emp_id INT PRIMARY KEY AUTO_INCREMENT,
  7. emp_name VARCHAR(15),
  8. department_id INT
  9. );
  10. ALTER TABLE emp2
  11. ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);
  12. SELECT * FROM information_schema.table_constraints
  13. WHERE table_name = 'emp2';

约束等级

  • Cascade方式:在父表上update/delete记录时,同步update/delete掉子表的匹配记录
  • Set null方式:在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为not null
  • No action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
  • Restrict方式:同no action, 都是立即检查外键约束
  • Set default方式:在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别 。

如果没有指定等级,就相当于Restrict方式。
对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

  1. # on update cascade on delete set null
  2. CREATE TABLE dept(
  3. did INT PRIMARY KEY, #部门编号
  4. dname VARCHAR(50) #部门名称
  5. );
  6. CREATE TABLE emp(
  7. eid INT PRIMARY KEY, #员工编号
  8. ename VARCHAR(5), #员工姓名
  9. deptid INT, #员工所在的部门
  10. FOREIGN KEY (deptid) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE SET NULL
  11. #把修改操作设置为级联修改等级,把删除操作设置为set null等级
  12. );
  13. INSERT INTO dept VALUES(1001,'教学部');
  14. INSERT INTO dept VALUES(1002, '财务部');
  15. INSERT INTO dept VALUES(1003, '咨询部');
  16. INSERT INTO emp VALUES(1,'张三',1001); #在添加这条记录时,要求部门表有1001部门
  17. INSERT INTO emp VALUES(2,'李四',1001);
  18. INSERT INTO emp VALUES(3,'王五',1002);
  19. UPDATE dept
  20. SET did = 1004
  21. WHERE did = 1002;
  22. DELETE FROM dept
  23. WHERE did = 1004;
  24. SELECT * FROM dept;
  25. SELECT * FROM emp;

删除外键约束

  1. (1)第一步先查看约束名和删除外键约束
  2. SELECT * FROM information_schema.table_constraints
  3. WHERE table_name = '表名称';#查看某个表的约束名
  4. ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
  5. 2)第二步查看索引名和删除索引。(注意,只能手动删除)
  6. SHOW INDEX FROM 表名称; #查看某个表的索引名
  7. ALTER TABLE 从表名 DROP INDEX 索引名;
  1. SELECT * FROM information_schema.table_constraints
  2. WHERE table_name = 'emp1';
  3. #删除外键约束
  4. ALTER TABLE emp1
  5. DROP FOREIGN KEY fk_emp1_dept_id;
  6. #再手动的删除外键约束对应的普通索引
  7. SHOW INDEX FROM emp1;
  8. ALTER TABLE emp1
  9. DROP INDEX fk_emp1_dept_id;