外键约束(Foreign key)
表格中可以有多个列被设置成外键约束
当前列的值可以为空 可以重复
当前的值不能随便填写 值需要去列外一张表格内找寻
列外一张表格的列 的约束是(主键约束\唯一约束)
用来演示的两种表格
#两个表没有关系的表格
mysql> select * from cla ;
#主键 非空 唯一
+-------+-------+--------+
| class | name | loc | #三个列都设置了 约束
+-------+-------+--------+
| 6 | JAVA6 | 广州 |
| 7 | JAVA7 | 杭州 |
+-------+-------+--------+
mysql> select * from student;
主键 唯一
+------+---------+------+------+-------+
| id | name | age | sex | class |-->设置成外键约束 值在上面的按个表格classz中找寻
+------+---------+------+------+-------+
| 1 | 张三 | 18 | 男 | 6 |
| 2 | 李四 | 19 | 女 | 7 |
| 3 | 王五 | 19 | 男 | 6 |
| 4 | 赵六 | 18 | 女 | 7 |
| 5 | 钱t七 | 18 | 男 | 6 |
+------+---------+------+------+-------+
5 rows in set (0.00 sec)
#添加外键约束 需要参考另一个表的列的值
#如果外键的列内有数据 里面的数据必须要和另一个表中的列的数据数据一致 不然会报错
#语句 alter table 表名 add constraint 约束名 foreign key(key) references 另一个表名(列) ;
mysql> alter table student add constraint fk_class_class foreign key(class) references cla(class) ;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
#查询看下外键约束
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES |PRI | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
| age | int(5) | YES | | NULL | |
| sex | varchar(1) | YES | | NULL | |
| class | int(1) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
# PRI 主键 UNI 唯一 MUL 外键-->multiple(关联 多样)
5 rows in set (0.01 sec)
#查看外键约束的名字是我们起的外键名
mysql> show keys from student ;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 1 | fk_class_class | 1 | class | A | 2 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(5) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`class` int(1) DEFAULT NULL,
PRIMARY KEY (`id`), # 主键约束
UNIQUE KEY `name` (`name`)#唯一键约束
KEY `fk_class_class` (`class`),###记录的是另一个表的中别被当前表外键约束参拷的 信息
CONSTRAINT `fk_class_class` FOREIGN KEY (`class`) REFERENCES `cla` (`class`)#记录的本表外键约束的 信息
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#如何删除外键约束
#语句 alter table student deop foreign key 约束名 ;
mysql> alter table student drop foreign key fk_class_class ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#在查询看一下 外键约束还在???? 往下看
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES |PRI | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
| age | int(5) | YES | | NULL | |
| sex | varchar(1) | YES | | NULL | |
| class | int(1) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
# 看一下创建表格的信息 发现与外键约束的一起的信息没有删除干净 需要再次删除
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(5) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`class` int(1) DEFAULT NULL,
PRIMARY KEY (`id`),##主键约束
UNIQUE KEY `name` (`name`)##唯一约束
KEY `fk_class_class` (`class`)#外键约束的信息没删除干净
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#删除 KEY `fk_class_class` (`class`)#外键约束的信息没删除干净
#语句 alter table student drop key 约束名 ;
mysql> alter table student drop key fk_class_class ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再看一下 这样才是真正的把外键约束删除干净了
mysql> desc student ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES |PRI | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
| age | int(5) | YES | | NULL | |
| sex | varchar(1) | YES | | NULL | |
| class | int(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#这就是我们创建表格那时候的信息
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(5) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`class` int(1) DEFAULT NULL
PRIMARY KEY (`id`), #主键约束
UNIQUE KEY `name` (`name`)#唯一键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
################################
#添加外键的名语句可以简写 但不建议简写
#语句 later table 表名 add foreign key(列) rederences 另一个表格(列) ;
mysql> alter table student add foreign key(class) references cla(class);
Query OK, 5 rows affected (0.17 sec)
Records: 5 Duplicates: 0 Warnings: 0
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(5) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`class` int(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `class` (`class`), ####注意 外键约束名 默认是 当前表中的列名
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class`) REFERENCES `cla` (`class`)######注意 这个key的列名 不是我们主键起的主键名也不是 默认的列名 在删除这个外键约束的时候还要找到这个 key的名字才可以删除 比较麻烦
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#先删除
mysql> alter table student drop foreign key student_ibfk_1 ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#后删除
mysql> alter table student drop key class ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
1 row in set (0.00 sec)
检查约束(Check) 在mysql数据中检查约束不好用
列在存值的时候做一个细致的检查 范围是否合理
alter table student add constraint ck_age check(age<15 and <30);