MySql---外键复习
mysql --- 复习 外键
2023-09-14 09:13:35 时间
外键复习
MySQL外键约束(FOREIGN KEY)
- MySQL 外键约束(FOREIGN KEY)用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。
- 外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键的值必须等于另一个表中主键的某个值。
- 外键是表的一个字段,不是本表的主键,但对应另一个表的主键。定义外键后,不允许删除另一个表中具有关联关系的行。
- 外键的主要作用是保持数据的一致性、完整性。例如,部门表 tb_dept 的主键是 id,在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。
主表和从表
- 主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表。
- 从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的表就是从表。
选取设置 MySQL 外键约束的字段
定义一个外键时,需要遵守下列规则:
- 父表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则父表与子表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性。
- 必须为父表定义主键。
- 主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
- 外键中列的数目必须和父表的主键中列的数目相同,因为有组合主键和组合外键。
- 外键中列的数据类型必须和父表主键中对应列的数据类型相同。
在创建表时设置外键约束
在数据表中创建外键使用 FOREIGN KEY
关键字,具体的语法规则如下:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…]
REFERENCES <主表名> 主键列1 [,主键列2,…]
其中:外键名
为定义的外键约束的名称,一个表中不能有相同名称的外键;字段名
表示子表被外健约束的字段名;主表名
即被子表外键所依赖的表的名称;主键列
表示主表中定义的主键列或者列组合。
部门和员工案例演示
创建父表和从表
USE test1;
#部门表--父表先创建
#主表: 可以约束其他表的字段值的表
CREATE TABLE depart(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
d_name VARCHAR(10)
);
#员工表(从表) :被约束的表,外键写在从表里面
CREATE TABLE emp(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
e_name VARCHAR(10),
d_id INT(4),#这里外键列的类型要和主表的主键类型一致
#写外键约束
#定义了一个emp_dept_fk名字的外键,本表中d_id字段为外键,被参考表dept的id字段所约束
CONSTRAINT emp_dept_fk FOREIGN KEY(d_id) REFERENCES depart(id)
);
父表插入数据
INSERT INTO depart (d_name) VALUES ("研发部"),("人事部");
从表插入数据
#添加一个符合外键约束的数据
INSERT INTO emp (e_name,d_id) VALUES("大忽悠",1);
如果添加不符合外键约束的数据,会报错
这里员工的d_id列的值被depart父表的主键列所约束,即从表的d_id的值必须属于父表的主键列集合里面的id值
小总结
- 被约束的表成为副表,约束别人的表成为主表,外键设置在副表上
- 主表(参考表)的参考字段通常为主键
- 添加数据时,必须先添加主表,后添加副表
- 修改数据时,必须先修改副表,再修改主表
最后一个修改解释:
例如: 部门表id为3的部门下面有员工,向把部门id的值从3改到4
1.先修改副表,先把员工表外键id=3的员工先挂到其他部门下面,解除部门表id=3和员工表外键id=3的关系
2.再修改主表,解除挂钩之后,就可以修改部门表id的值从3到4,改完之后,再把之前临时挂到其他部门的员工给再挂回到改好的部门
注意事项
#添加一个符合外键约束的数据
INSERT INTO emp (e_name,d_id) VALUES("大忽悠",1);
#添加一个不符合外键约束的数据--该插入语句执行四次
INSERT INTO emp(e_name,d_id) VALUES("小朋友",10);
#添加一个符合外键约束的数据
INSERT INTO emp (e_name,d_id) VALUES("大朋友",2);
注意观察主键id自增
- 如果插入的数据因为不符合外键约束插入失败了,主键id依然会自增
想要删除父表中编号为1的部门,就必须先将该部门下的所有员工删除
级联操作
当有了外键约束的时候,必须先修改或删除副表中的所有关联数据,才能修改或删除主表
但是,我们希望直接修改或删除主表数据,从而影响副表数据,如删除部门表的某个部门,直接自动删除员工表中被删除部门对于的所有员工
这就是级联操作
格式
在定义外键的时候追加以下内容:
级联修改:
ON UPDATE CASCADE
级联删除:
ON DELETE CASCADE
这里选择修改表的时候,添加外键约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
设置级联修改和删除关系
#先将表之前的外键约束删除
ALTER TABLE emp DROP FOREIGN KEY emp_depart_fk;
#修改表时,增加外键约束和级联约束
ALTER TABLE emp ADD CONSTRAINT emp_depart_fk FOREIGN KEY(d_id) REFERENCES depart(id)
ON UPDATE CASCADE ON DELETE CASCADE;
测试级联操作
父表
从表
修改研发部的部门id,对应员工所属的部门id是否变化
UPDATE depart SET id=3 WHERE d_name="研发部";
删除研发部,看研发部下面的员工,是否被自动删除
#删除部门表中部门编号为1的部门
DELETE FROM depart WHERE id=3;
相关文章
- MySQL数据库设计规范
- 【Mysql安装】Mac下安装mysql
- linux - mysql 异常:MySQL Daemon failed to start.
- mysql事务
- 【备份】使用mysqldump 实现rename database name(mysql数据库改名称)
- MySQL--执行mysql脚本及其脚本编写
- MySQL 千万级 数据库或大表优化
- MySQL运维---延迟从库和半同步复制
- MySQL运维---慢日志与备份
- paip.提升性能---mysql 性能 测试以及 参数调整.txt
- Linux随笔(安装ftp,安装jdk,安装 tomcat,安装redis,安装MySQL,安装svn)
- error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
- MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写
- Python:mysql-connector-python模块对MySQL数据库进行增删改查
- 【数据库系统】SQLite vs MySQL vs PostgreSQL:关系数据库管理系统的比较
- Mysql如何查字段的长度,Mysql中length()、char_length()的区别
- Mysql之keepalived双主搭建
- mysql---用户和权限管理复习
- Python可视化数据分析09、MySQL读写
- 【高可用MySQL解决方案】centos7配置mysql主从复制
- Mycat启动正常但无法连接ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql
- [ 云计算 | Azure ] 配置Azure Database for MySQL数据库的时区