往事悠悠君莫问,回头。槛外长江空自流。

——王安石《南乡子》

数据库表的约束和表之间的关系

1. 数据库表的约束

约束的作用:

  • 对表中的数据进行限制,保证数据的正确性、有效性和完整性。

  • 一个表如果添加了约束,不正确的数据将无法插入到表中。

  • 约束在创建表的时候添加比较合适

约束的种类

约束名 约束关键字
主键 primary key
唯一 unique
非空 not null
外键 foreign key
检查约束 check 注:mysql 不支持

1.1 主键约束

用来唯一标识数据库中的每一条记录

哪个字段应该作为表的主键?

通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
如:身份证,学号不建议做成主键

1.1.1 创建主键

创建主键关键字: primary key
主键的特点:

  • 非空 not null
  • 唯一 unique
1
2
3
4
5
6
-- 创建主键的两种方式

-- 1.在创建表的时候给字段添加主键
字段名 字段类型 primary key
-- 2.在已有表中添加主键
alter table 表名 add primary key(字段名);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 演示
创建表学生表 student, 包含字段(id, name, age)将 id做为主键
create table student (
id int primary key, -- id为主键
name varchar(20),
age int
)
desc student;



-- 插入重复的主键值
insert into student values (1, '上官婉儿', 18);
-- 错误代码:1062 Duplicate entry '1' for key'PRIMARY'
insert into student values (1, '上官', 20);

select * from student;


-- 插入 null的主键值, Column 'id' cannot be null
insert into st5 values (null, '上官婉儿', 20);

1.1.2 删除主键

1
2
3
4
-- 删除student表的主键
alter table student drop primary key;
-- 添加主键
alter table student add primary key(id);

1.1.3 主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

auto_increment表示自动增长(字段类型必须是整数类型)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
创建表学生表 student, 包含字段(id, name, age)将 id做为主键
create table student (
id int primary key auto_increment, -- id为主键
name varchar(20),
age int
)

-- 插入数据
insert into student (name,age) values ('小乔',18);
insert into student (name,age) values ('大乔',20);
-- 另一种写法
insert into student values(null,'周瑜',35);

select * from student;
1.1.3.1 修改自增长的默认值起始值

默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法

创建表的时候修改自增长起始值

1
2
3
4
5
6
7
8
9
10
11
create table 表名 (
列名 int primary auto_increment
)auto_increment=起始值; -- 修改自增长的默认值起始值

-- 指定起始值为 1000
create table student (
id int primary key auto_increment,
name varchar(20)
) auto_increment = 1000;

insert into st4 values (null, '孔明');

创建好表以后修改起始值

1
2
3
4
alter table 表名 auto_increment=起始值;

alter table student auto_increment = 2000;
insert into student values (null, '刘备');
1.1.3.2 delete 和 truncate对自增长的影响
  • delete 删除所有的记录之后,自增长没有影响。
  • truncate 删除以后,自增长又重新开始。

1.2 唯一约束

什么是唯一约束?

就是表中某一列不能出现重复的值。

1
2
-- 唯一约束的基本语法格式
字段名 字段类型 unique;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 创建学生表 student, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
create table student (
id int,
name varchar(20) unique
)

-- 添加一个同名的学生
insert into student values (1, '张三');
select * from student;

-- Duplicate entry '张三' for key 'name'
insert into student values (2, '张三');


-- 重复插入多个 null会怎样?
insert into student values (2, null);
insert into student values (3, null);

-- null没有数据,不存在重复的问题
-- null没有数据,不存在重复的问题
-- null没有数据,不存在重复的问题

1.3 非空约束

什么是非空约束?

就是某一列不能为 null。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 非空约束的基本语法格式
字段名 字段类型 not null;

-- 创建表学生表 student, 包含字段(id,name,gender)其中 name不能为 null
create table student (
id int,
name varchar(20) not null,
gender char(1)
);


-- 添加一条记录其中姓名不赋值
insert into student values (1,'张三疯','男');
select * from student;

-- Column 'name' cannot be null
insert into student values (2,null,'男');

1.2.1 非空约束默认值

什么是非空约束默认值?

字段名 字段类型 default 默认值;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建一个学生表 student4,包含字段(id,name,address),地址默认值是广州
create table student (
id int,
name varchar(20),
address varchar(20) default '广州'
)

-- 添加一条记录,使用默认地址
insert into student values (1, '李四', default);
select * from student;

insert into student (id,name) values (2, '李白');

-- 添加一条记录,不使用默认地址
insert into student values (3, '李四光', '深圳');

如果一个字段设置了非空与唯一约束,该字段与主键的区别?

  • 主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。
  • 自增长只能用在主键上

1.4 外键约束

1.4.1 单表的缺点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 6 条数据
create table emp (
id int primary key auto_increment,
name varchar(30),
age int,
dep_name varchar(30),
dep_location varchar(30)
);

-- 添加数据
insert into emp (name, age, dep_name, dep_location) values ('张三', 20, '研发部', '广州');
insert into emp (name, age, dep_name, dep_location) values ('李四', 21, '研发部', '广州');
insert into emp (name, age, dep_name, dep_location) values ('王五', 20, '研发部', '广州');
insert into emp (name, age, dep_name, dep_location) values ('老王', 20, '销售部', '深圳');
insert into emp (name, age, dep_name, dep_location) values ('大王', 22, '销售部', '深圳');
insert into emp (name, age, dep_name, dep_location) values ('小王', 18, '销售部', '深圳');

以上数据表的缺点:

  • 数据冗余
  • 后期还会出现增删改的问题

1.4.2 解决方案

分成 2 张表,设置主表和从表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 创建部门表(id,dep_name,dep_location)

-- 一方,主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);

-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)

-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;


-- 添加员工,dep_id表示员工所在的部门
insert into employee (name, age, dep_id) values ('张三', 20, 1);
insert into employee (name, age, dep_id) values ('李四', 21, 1);
insert into employee (name, age, dep_id) values ('王五', 20, 1);
insert into employee (name, age, dep_id) values ('老王', 20, 2);
insert into employee (name, age, dep_id) values ('大王', 22, 2);
insert into employee (name, age, dep_id) values ('小王', 18, 2);
select * from employee;

当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,实际应用中不能出现这种情况。

employee 的 dep_id 中的数据只能是 department 表中存在的 id

1.4.3 外键约束

什么是外键?

在从表中与主表主键对应的那一列,如:员工表中dep_id

==主表==: 一方,用来约束别人的表
==从表==: 多方,被别人约束的表

1.4.3.1 创建外键约束

新建表时增加外键

1
[constraint] [外键约束名称] foreign key (外键字段名) references 主表名(主键字段名);

已有表增加外键

1
alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 1.删除副表/从表 employee
drop table employee;

-- 2.创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
constraint emp_depid_fk foreign key (dep_id) references department(id) -- 创建外键约束
)
-- 3.正常添加数据
insert into employee (name, age, dep_id) values ('张三', 20, 1);
insert into employee (name, age, dep_id) values ('李四', 21, 1);
insert into employee (name, age, dep_id) values ('王五', 20, 1);
insert into employee (name, age, dep_id) values ('老王', 20, 2);
insert into employee (name, age, dep_id) values ('大王', 22, 2);
insert into employee (name, age, dep_id) values ('小王', 18, 2);

select * from employee;

-- 4.部门错误的数据添加失败
-- 插入不存在的部门
-- Cannot add or update a child row: a foreign key constraint fails
insert into employee (name, age, dep_id) values ('老张', 18, 6);

1.4.3.2 删除外键

alter table 从表 drop foreign key 外键名称;

1
2
3
4
5
6
7
-- 删除 employee表的 emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk;


-- 在 employee表存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);
1.4.3.3 外键的级联

什么是级联操作?

在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 出现新的问题
select * from employee;
select * from department;

/*
要把部门表中的 id值 2,改成 5,能不能直接更新呢?会报出一下错误:
Cannot delete or update a parent row: a foreign key constraint fails
*/
update department set id=5 where id=2;



/*
要删除部门 id等于 1的部门, 能不能直接删除呢?会报出一下错误:
Cannot delete or update a parent row: a foreign key constraint fails
*/
delete from department where id=1;
级联操作语法 描述
on update cascade 级联更新,只能是创建表的时候创建级联关系,
更新主表中的主键,从表中的外键列也自动同步更新
on delete cascade 级联删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 删除 employee表,重新创建 employee表,添加级联更新和级联删除
drop table employee;

create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade -- 创建外键约束
)


-- 再次添加数据到员工表和部门表
insert into employee (name, age, dep_id) values ('张三', 20, 1);
insert into employee (name, age, dep_id) values ('李四', 21, 1);
insert into employee (name, age, dep_id) values ('王五', 20, 1);
insert into employee (name, age, dep_id) values ('老王', 20, 2);
insert into employee (name, age, dep_id) values ('大王', 22, 2);
insert into employee (name, age, dep_id) values ('小王', 18, 2);


-- 删除部门表?能不能直接删除?
drop table department;


-- 把部门表中 id等于 1的部门改成 id等于 10
update department set id=10 where id=1;
select * from employee;
select * from department;

-- 删除部门号是 2的部门
delete from department where id=2;

1.5 数据约束小结

约束名 关键字 说明
主键 primary key 唯一,非空
默认 default 如果一列没有值,使用默认值
非空 not null 这一列必须有值
唯一 unique 这一列不能有重复值
外键 foreign key 主表中主键列,在从表中外键列

2. 表与表之间的关系

概念:

  • 现实生活中,实体与实体之间肯定是有关系的,比如:爸爸和儿子,部门和员工,老师(喵喵)和学生(dd)等。那么我们在设计表的时候,就应该体现出表与表之间的这种关系!
表与表之间的三种关系 关系的维护
一对多:最常用的关系 部门和员工 主外键的关系
多对多:学生选课表 和 学生表, 一门课程可以有多个学生选择,一个学生选择多门课程 中间表,两个一对多
一对一:相对使用比较少。员工表 简历表, 公民表 护照表 特殊一对多,从表中的外键设置为唯一,从表中的主键又是外键

2.1 一对多

一对多(1 : n)

例如:班级和学生,部门和员工,客户和订单,分类和商品

*一对多建表原则: *

​ 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

2.2 多对多

多对多(m : n)

例如:老师和学生,学生和课程,用户和角色

多对多关系建表原则:

​ 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。

2.3 一对一

一对一(1: 1)

在实际的开发中应用不多.因为一对一可以创建成一张表。

两种建表原则:

一对一的建表原则 说明
外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 unique
外键是主键 主表的主键和从表的主键,形成主外键关系