外键 foreign key
外键:一个表的数据,依赖另一张表主键列的数据,如果在主键列没有出现的值,是不能够出现在外键字段的。
创建外键的条件:
1)存储引擎必须是innodb(需了解)
2)相关联字段数据类型要一致
3)最好在外键列上建索引(目的是为了减少扫描范围,不创建也可以,只是影响性能)
例:
class 班级表
student 学生表
class 父表 student 子表
CLASS_ID NAME AGE
ID CLASS 1 jim 18
1 yun 0215 1 tom 19
2 yun 0308 2 xiao 20
3 yun 0316 2 ming 21
4 yun 0411 5 hong 19
创建父表
MariaDB [test]> create table class (ID int primary key,class char(20));
创建子表
MariaDB [test]> create table student (CLASS_ID int,NAME char(20),AGE int,foreign key(CLASS_ID) references class(ID));
向父表内插入数据
MariaDB [test]> insert into class values(1,'yun0215'),
-> (2,'yun0308'),
-> (3,'yun0316'),
-> (4,'yun0411');
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 1 | yun0215 |
| 2 | yun0308 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
向字表内插入数据
MariaDB [test]> insert into student values
-> (1,'jim',18),
-> (1,'tom',19),
-> (2,'xiao',20);
MariaDB [test]> insert into student values (5,'hong',19); //插入父表中不存在的班级号
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))
MariaDB [test]> delete from class where ID=1; //删除父表中有外键依赖的数据
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))
总结:
1)子表中的关联数据依赖于父表,不能向子表中插入父表中不存在的值
2)不能删除父表中被子表所依赖的记录
删除父表中被依赖的行的方法:
1)删除外键约束
2)指定级联操作的选项
on delete cascade 级联删除
on update cascade 级联更新
MariaDB [test]> drop table student;
MariaDB [test]> create table student (CLASS_ID int ,NAME char(20),AGE int,foreign key(CLASS_ID) references class (ID) on delete cascade on update cascade);
MariaDB [test]> insert into student values
-> (1,'jim',18),
-> (1,'tom',19),
-> (2,'xiao',20),
-> (2,'ming',21);
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 1 | jim | 18 |
| 1 | tom | 19 |
| 2 | xiao | 20 |
| 2 | ming | 21 |
+----------+------+------+
MariaDB [test]> delete from class where ID=2;
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 1 | yun0215 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 1 | jim | 18 |
| 1 | tom | 19 |
+----------+------+------+
MariaDB [test]> update class set ID=2 where ID=1;
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 2 | yun0215 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 2 | jim | 18 |
| 2 | tom | 19 |
+----------+------+------+
总结:有了级联删除和级联更新选项,父表中的数据发生删除或者更新时,子表中相关数据也会发生相应的变化。
删除外键
语法:
alter table 表名 drop foreign key 外键的名字(不是字段名)
查看外键的名字
MariaDB [test]> show create table student\G; //红色字体为外键的名字
**************** 1. row **************
Table: student
Create Table: CREATE TABLE `student` (
`CLASS_ID` int(11) DEFAULT NULL,
`NAME` char(20) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
KEY `CLASS_ID` (`CLASS_ID`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
ERROR: No query specified
MariaDB [test]> alter table student drop foreign key student_ibfk_1;
MariaDB [test]> show create table student\G;
*************** 1. row ***************
Table: student
Create Table: CREATE TABLE `student` (
`CLASS_ID` int(11) DEFAULT NULL,
`NAME` char(20) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
KEY `CLASS_ID` (`CLASS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
如果使用>desc 表名 这种方法查看外键,会发现外键依然存在,但是没有外键效果。