外键
核心思想:保证数据一致性
插入记录时,表头值在另一张表 表头值范围内选择。
外键的使用规则:
表存储引擎必须是innodb (在进阶课程里讲 现在仅需要知道如何指定表使用innodb存储引擎)
字段类型要一致
被参照字段必须要是索引类型的一种(通常是 primary key)
创建外键命令
create table 库.表(
表头列表 ,
foreign key(表头名) #指定外键
references 库.表(表头名) #指定参考的表头名
on update cascade #同步更新
on delete cascade #同步删除
)engine=innodb;
需求: 仅给公司里已经入职的员工发工资
首先创建存储员工信息的员工表
员工表 yg
员工编号 姓名
yg_id name
1 bob
2 bob
#创建员工表
create table db1.yg (
yg_id int primary key auto_increment ,
name char(16)
) engine=innodb;
#查看表的存储引擎
show create table db1.yg \G
#没有表记录
select * from db1.yg;
创建工资表
工资表 gz
员工编号 工资
gz_id pay
#创建工资表 指定外键表头
mysql> create table db1.gz(
-> gz_id int , pay float(7,2),##7位数,2个小数位,
-> foreign key(gz_id) references db1.yg(yg_id)
-> on update cascade on delete cascade
-> )engine=innodb ;
通过查看表结构查看不到外键的设置 MUL是 普通索引的标志
mysql> desc db1.gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | YES | MUL | NULL | |
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#通过显示建表命令查看外键 ,并获取外键名
Mysql> show create table 库名.表名 \G
查看工资表外键
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float(7,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
删除外键命令
mysql> alter table 库.表 drop foreign key 外键名;
例子
删除工资表的外键
mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;
查看不到外键
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float(7,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
添加外键命令
mysql> alter table 库.表 add foreign key(表头名) references 库.表(表头名)
on update cascade on delete cascade;
例子
工资表添加外键
mysql> alter table db1.gz add foreign key(gz_id) references db1.yg(yg_id)
on update cascade on delete cascade ;
查看外键
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float(7,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
外键验证:
1 外键字段的值必须在参考表字段值范围内
2 验证同步更新( on update cascade)
3 验证同步删除( on delete cascade)
1、外键字段的值必须在参考表字段值范围内
员工表插入记录
mysql> insert into db1.yg (name) values ("jerry"),("tom");
mysql> select * from db1.yg;
工资表插入记录
mysql> insert into db1.gz values(1,50000);
mysql> insert into db1.gz values(2,50000);
mysql> select * from db1.gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 50000.00 |
| 2 | 50000.00 |
+-------+----------+
2 rows in set (0.00 sec)
#没有编号3的员工 工资表插入记录报错
mysql> insert into db1.gz values(3,50000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
员工表 插入编号3的员工
mysql> insert into db1.yg (name) values ("Lucy");
mysql> select * from db1.yg;
有编号3的员工 工资表插入记录成功
mysql> insert into db1.gz values(3,50000);
2、验证同步更新( on update cascade)
查看员工表记录
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 2 | tom |
| 3 | lucy |
+-------+-------+
3 rows in set (0.00 sec)
把yg表里编号是3的改成9
mysql> update db1.yg set yg_id=9 where yg_id=3;
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 2 | tom |
| 9 | lucy |
+-------+-------+
3 rows in set (0.00 sec)
工资表里编号是3的自动变成 9
mysql> select * from db1.gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 50000.00 |
| 2 | 50000.00 |
| 9 | 50000.00 |
+-------+----------+
3 rows in set (0.00 sec)
mysql>
3、验证同步删除( on delete cascade)
删除前查看员工表记录
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 2 | tom |
| 9 | lucy |
+-------+-------+
3 rows in set (0.00 sec)
删除编号是2的员工
mysql> delete from db1.yg where yg_id=2;
Query OK, 1 row affected (0.04 sec)
删除后查看
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 9 | lucy |
+-------+-------+
2 rows in set (0.00 sec)
查看工资表也没有编号是2的工资了
mysql> select * from db1.gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 50000.00 |
| 9 | 50000.00 |
+-------+----------+
2 rows in set (0.00 sec)
mysql>
外键使用注意事项
#被参考的表不能删除
mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
给gz表的gz_id表头 加主键标签
保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
# 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键
delete form db1.gz;
alter table db2.gz add primary key(gz_id);
保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
mysql> insert into db1.gz values (1,53000); 报错
mysql> insert into db1.gz values (9,58000); 报错
mysql> insert into db1.gz values (NULL,80000); 报错
sql-----字段约束----外键
最新推荐文章于 2024-02-20 10:41:32 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)