开发工程师必备————【Day13】数据库约束条件

原创已于 2022-08-17 08:19:46 修改 · 327 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #mysql

于 2022-08-17 00:00:16 首次发布

本文详细介绍了数据库中的主键和外键约束，包括主键的非空和唯一性、自增特性，以及外键在一对多、一对一和多对多关系中的应用。通过实例展示了如何创建和管理这些约束，以及级联更新和删除的功能。同时，讨论了在实际工作中是否使用外键的考量因素，并给出了多对多关系的解决方法。最后，给出了几个具体场景下的表关系判断及创建示例。

今日内容概要

约束条件之主键
约束条件之外键
外键字段SQL语句（Foreign Key）
表关系之多对多
表关系之一对一

约束条件之主键

1.primary key 主键
（1）但从约束角度上而言主键等价于非空且唯一：not null；unique。
代码展示：
create table t1(
	id int primary key,
	name varchar(32)
);
验证非空（not null）：
insert into t1(name) values('jason')
报错：ERROR 1364 (HY000): Field 'id' doesn't have a default value
验证唯一性（unique）：
insert into t1(id) values(1);
第一次成功：Query OK, 1 row affected (0.05 sec)
insert into t1(id) values(1);
第二次报错：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
（2）InnoDB存储引擎规定一张表必须有且只有一个主键；

如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐蔽的字段作为主键（主键可以加快数据查询：例如新华字典的目录）
如果创建的表中没有创建主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将字段设置为主键（从上往下找）。

代码展示：
下面代码默认会把nid设为主键！！！
create table t2(
	nid int not null unique,
	sid int not null unique,
	uid int not null unique,
	name varchar(32)
);
（3）创建表的时候都应该有一个‘id’字段，并且该字段应该作为主键。
uid；sid；pid；gid；cid；id
单列主键：
id int primary key
联合主键：
sid int,
nid int,
primary key(sid,nid)

2.auto_increment自增
（1）该约束条件不能单独使用，必须跟在键后面（主要配合主键一起使用！！！）
代码展示：
create table t3(
	id int auto_increment
);
报错：there can be only one auto column and it must be defined as a key


create table t4(
	id int primary key auto_increment,
	name varchar(32)
);

（2）自增的特点：

自增的操作不会因为执行删除数据的操作而回退或者重置。
delete from

如果需要重置主键，则需要格式化表。
truncate 表名;      # 删除表数据并重置主键值

约束条件之外键

1.外键前戏
需要创建一张员工表：
id；name；gender；dep_name；dep_desc；
上述表的缺陷：
（1）表结构不清晰，到底是员工表还是部门表；
（2）字段数据反复存取，浪费存储空间；
（3）表的扩展性极差，牵一发动全身；
优化操作>>>拆表：
员工表：id；name；gender；
部门表：id；dep_name；dep_desc；
拆表之后:
解决了上述的三个问题，但是出现了一个致命的缺陷（不知道数据信息，两个表数据之间关系不确定。）。
解决措施：在员工表后添加一个部门编号（dep_id）
id；name；gender；dep_id；
添加一个部门编号字段填写部门数据的主键值。
外键字段：
专门用于记录表与表之间数据的关系。

2.外键字段的创建
外键字段是用来记录表与表之间数据的关系，而数据的关系有四种：
一对多关系；
多对多关系；
一对一关系；
没有关系
（1）“表关系之一对多推导过程”
使用方法：换位思考
步骤一：针对员工表和部门表判断数据关系的方式：

先站在员工表的角度：
问：一条员工数据能否对应多条部门数据；
翻：一名员工能否属于多个部门；
答：不可以
在站在部门表的角度：
问：一条部门数据能否对应多条员工数据；
翻：一个部门能否拥有多个员工；
答：可以

步骤二：完成上述两个换位思考之后得出的答案：一个可以一个不可以；
步骤三：那么表关系就是“一对多”,并且部门是一，员工是多；
最终结论：针对‘一对多’的关系，外键字段建在多的一方；
ps：没有多对一，统一称为‘一对多’。

外键字段SQL语句（Foreign Key）

1.外键编写思路：
（1）先写普通字段
（2）然后在写外键字段
代码展示：

枚举性别并设置默认值为male；
create table emp(
	id int primary key auto_increment,
	name varchar(32),
	genser enum('male','female','others') default 'male',
	dep_id int,
	foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
	dep_name varchar(32),
	dep_desc varchar(32)
);

请添加图片描述
创建外键注意事项：
第一步：创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
第二步：插入表数据的时候针对外键字段只能填写被关联表字段已经出现过的数据值。如果出现未出现的数据值则会报错！！！

插入dep数据：
mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 教书育人     |
|  2 | 安保部    | 维护治安     |
+----+-----------+--------------+
2 rows in set (0.00 sec)


插入emp数据：
mysql> insert into emp(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | kevin | male   |      2 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)


插入dep未出现的数据值id贼会报错！！！
mysql> insert into emp(name,dep_id) values('oscar',3);
报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

第三步：被关联字段无法修改和删除（操作限制性太强）

删除：
mysql> delete from dep where id=2;
报错：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))


修改：
mysql> update dep set id=200 where id=2;
报错：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

2.级联更新、级联删除
（1）概念：被关联数据一旦变动关联的数据同步变动
代码展示：

create table emp1(
	id int primary key auto_increment,
	name varchar(32),
 	gender enum('male','female','others') default 'male',
  dep_id int,
  foreign key(dep_id) references dep1(id) 
  on update cascade  # 级联更新 
  on delete cascade  # 级联删除
);

create table dep1(
	id int primary key auto_increment,
  	dep_name varchar(32),
   dep_desc varchar(32)
);

3.扩展知识点
（1）在实际工作中，很多时候可能并不会使用外键；
因为外键增加了表之间的耦合度，不便于单独操作，资源消耗增加。
（2）我们为了能够描述出表数据的关系，又不想使用外键；
自己通过写SQL 建立代码层面的关系

表关系之多对多

1.前戏
以书籍表和作者表为例
使用方法：换位思考

先站在书籍表的角度
问:一条书籍数据能否对应多条作者数据
答:可以
2.再站在作者表的角度
问:一条作者数据能否对应多条书籍数据
答:可以

最终总结:两边都可以，那么表数据关系就是’多对多’。

2.SQL编写
针对多对多表关系外键字段不能建在任意一方!!!
（1）多对多的关系不可以在原表上修改，结果会报错，会造成两个表死锁现象！！！
代码展示：

create table book(
	id int primary key auto_increment,
	title varchar(32),
	author_id int,
	foreign key(author_id) references author(id) 
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
);
报错：ERROR 1215 (HY000): Cannot add foreign key constraint

create table author(
	id int primary key auto_increment,
	name varchar(32),
	book_id int,
	foreign key(book_id) references book(id) 
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
);
报错：ERROR 1215 (HY000): Cannot add foreign key constraint

（2）解决方式：需要单独开设第三张关系表存储数据关系
代码展示：

第三方表（book2author）保存author与book两表之间的关系！！！

create table book(
	id int primary key auto_increment,
	title varchar(32)
);

create table author(
	id int primary key auto_increment,
	name varchar(32)
);

create table book2author(
	id int primary key auto_increment,
	book_id int,
	foreign key(book_id) references book(id) 
	on update cascade  # 级联更新 
	on delete cascade, # 级联删除
	author_id int,
	foreign key(author_id) references author(id) 
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
);

请添加图片描述

表关系之一对一

1.前戏
以用户表和用户详情表
使用方法：换位思考

先站在用户表的角度
问:一个用户数据是否对应多个用户详情表
答:不可以
再站在用户详情表的角度
问:一个用户详情表是否对应多个用户
答:不可以

最终总结:两边都不可以那么先考虑是不是没有关系
如果有关系那么肯定就是’一对一’

2.SQL编写
针对’一对一’的表关系外键字段建在任何一张表都可以，但是建议你建在查询频率较高的表中便于后续查询。
代码展示：
用户表使用频率较高，所以外键字段建在用户表较为合适！！！
create table user(
	id int primary key auto_increment,
	detail_id int unique,
	foreign key(detail_id) references userDetail(id) 
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
);

create table userDetail(
 id int primary key auto_increment,
 phone bigint
);
这边注意一对一与一对多唯一的区别在于外键字段后一对一关系比一对多多加了unique关键字！！！从而实现了一对一的关系。

今日作业

3.判断下列表数据关系并自定义创建出表
服务器表与应用程序表
课程表与班级表
学生表与班级表
老师表与课程表
书籍表与出版社表

服务器表与应用程序表（多对多）

mysql> create table server1(
    -> server_id int primary key auto_increment,
    -> server_name varchar(32)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> create table client1(
    ->  client_id int primary key auto_increment,
    ->  client_name varchar(32)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> create table server2client(
    -> id int primary key auto_increment,
    -> ser_id int,
    -> foreign key(ser_id) references server1(server_id)
    -> on update cascade
    -> on delete cascade,
    -> cli_id int,
    -> foreign key(cli_id) references client1(client_id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.33 sec)

课程表与班级表（多对多）

mysql> create table course(
    -> course_id int primary key auto_increment,
    -> course_name varchar(32)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql> create table class(
    -> class_id int primary key auto_increment,
    ->  class_name varchar(32)
    -> );
Query OK, 0 rows affected (0.54 sec)

mysql> create table course2class(
    -> id int primary key auto_increment,
    -> cou_id int,
    -> foreign key(cou_id) references course(course_id)
    -> on update cascade
    -> on delete cascade,
    ->  cla_id int,
    -> foreign key(cla_id) references class(class_id)
    ->  on update cascade
    ->  on delete cascade
    -> );
Query OK, 0 rows affected (0.26 sec)

学生表与班级表（一对多）

mysql> create table class1(
    -> id int primary key auto_increment,
    ->  class_name varchar(32)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> create table student(
    ->  stu_id int primary key auto_increment,
    ->  stu_name varchar(32),
    ->  cla_id int,
    ->  foreign key(cla_id) references class1(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.24 sec)

老师表与课程表（一对一）

mysql> create table course1(
    -> id int primary key auto_increment,
    ->  course_name varchar(32)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql>  create table teacher(
    -> tea_id int primary key auto_increment,
    -> tea_name varchar(32),
    -> course1_id int unique,
    -> foreign key(course1_id) references course1(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.22 sec)

书籍表与出版社表（一对多）

mysql> create table bookstore(
    -> id int primary key auto_increment,
    -> bookstore_name varchar(32)
    -> );
Query OK, 0 rows affected (1.01 sec)

mysql> create table books(
    -> book_id int primary key auto_increment,
    -> books_name varchar(32),
    -> bookstore_id int,
    -> foreign key(bookstore_id) references bookstore(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.29 sec)