作用
限定某个表的某个字段的引用完整性。
比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分。
关键字
主表和从表/父表和子表
主表(父表):被引用的表,被参考的表
从表(子表):引用别人的表,参考别人的表
例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表。
例如:学生表、课程表、选课表:选课表的学生和课程要分别参考学生表和课程表,学生表和课程表是主表,选课表是从表。
特点
从表的外键列,必须引用/参考主表的主键或唯一约束的列。
为什么?因为被依赖/被参考的值必须是唯一的
在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
创建(CREATE)表时就指定外键约束的话,先创建主表,再创建从表。
删表时,先删从表(或先删除外键约束),再删除主表。
当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据
在“从表”中指定外键约束,并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。
例如:都是表示部门编号,都是int类型。
(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。(根据外键查询效率很高)
在创建表时添加外键约束
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致,逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样,也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列
#①先创建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
#②再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
# Cannot add foreign key constraint 报错!!!
# 上述操作报错,因为主表中的dept_id上没有主键约束或唯一性约束。
#③ 添加
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
DESC dept1;
#④ 再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
DESC emp1;
说明: (1)主表dept1必须先创建成功,然后才能创建emp1表,指定外键成功。 (2)删除表时,先删除从表emp1,再删除主表dept1
外键效果
#添加失败
INSERT INTO emp1
VALUES(1001,'Tom',10);
INSERT INTO dept1
VALUES(10,'IT');
#在主表dept1中添加了10号部门以后,我们就可以在从表中添加10号部门的员工
INSERT INTO emp1
VALUES(1001,'Tom',10);
#删除失败
DELETE FROM dept1
WHERE dept_id = 10;
#更新失败
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;
在创建表之后添加外键约束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);
ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp2';
约束等级
Cascade方式
:在父表上update/delete记录时,同步update/delete掉子表的匹配记录Set null方式
:在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为not nullNo action方式
:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作Restrict方式
:同no action, 都是立即检查外键约束Set default方式
:在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别 。
如果没有指定等级,就相当于Restrict方式。
对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
# on update cascade on delete set null
CREATE TABLE dept(
did INT PRIMARY KEY, #部门编号
dname VARCHAR(50) #部门名称
);
CREATE TABLE emp(
eid INT PRIMARY KEY, #员工编号
ename VARCHAR(5), #员工姓名
deptid INT, #员工所在的部门
FOREIGN KEY (deptid) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE SET NULL
#把修改操作设置为级联修改等级,把删除操作设置为set null等级
);
INSERT INTO dept VALUES(1001,'教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');
INSERT INTO emp VALUES(1,'张三',1001); #在添加这条记录时,要求部门表有1001部门
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);
UPDATE dept
SET did = 1004
WHERE did = 1002;
DELETE FROM dept
WHERE did = 1004;
SELECT * FROM dept;
SELECT * FROM emp;
删除外键约束
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
(2)第二步查看索引名和删除索引。(注意,只能手动删除)
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp1';
#删除外键约束
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
#再手动的删除外键约束对应的普通索引
SHOW INDEX FROM emp1;
ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;