MySql基础/05.MySQL数据库-表的约束和表之间的关系

发表于 2020-12-19 | 更新于 2020-12-19

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

——王安石《南乡子》

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

1. 数据库表的约束

约束的作用：

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

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

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

约束的种类

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

1.1 主键约束

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

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

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

1.1.1 创建主键

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

非空 not null

唯一 unique

-- 创建主键的两种方式

-- 1.在创建表的时候给字段添加主键
字段名 字段类型 primary key
-- 2.在已有表中添加主键
alter table 表名 add primary key(字段名);

-- 演示
创建表学生表 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 删除主键

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

1.1.3 主键自增

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

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

创建表学生表 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 语法

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

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, '孔明');

创建好表以后修改起始值

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;

-- 创建学生表 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。

-- 非空约束的基本语法格式
字段名 字段类型 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 默认值;

-- 创建一个学生表 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 单表的缺点

-- 创建一个员工表包含如下列(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 张表，设置主表和从表。

-- 创建部门表(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.删除副表/从表 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 外键名称;

-- 删除 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 外键的级联

什么是级联操作？

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

-- 出现新的问题
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	级联删除

-- 删除 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
外键是主键	主表的主键和从表的主键，形成主外键关系

本文作者: DuanChaojie

本文链接: http://www.itbuild.cn/2020/12/19/MySql%E5%9F%BA%E7%A1%80/05.MySQL%E6%95%B0%E6%8D%AE%E5%BA%93-%E8%A1%A8%E7%9A%84%E7%BA%A6%E6%9D%9F%E5%92%8C%E8%A1%A8%E4%B9%8B%E9%97%B4%E7%9A%84%E5%85%B3%E7%B3%BB/