06【数据库的约束】

6.1 数据库约束的概述

6.1.1 约束的作用：

一般在创建表的时候给表的字段添加各种约束，从而保证输入到表中的数据是正确的。保证数据的正确性，完整性和有效性。违反约束的数据是不能添加到表中去的。如果表已经存在，并且表中已经有数据，添加约束的时候如果表中的数据已经违反了现在要添加的约束，约束会添加失败。

6.1.2 约束种类：

约束名	约束关键字
主键	primary key
唯一	unique
默认	default
非空	not null
外键	foreign key … references
检查约束(mysql5.7不支持)	check

6.2 主键约束

6.2.1 主键的作用

用来唯一标识表中的每一行记录，在创建表的时候，每张表都应该创建一个主键，每个表只能有一个主键约束，只要有主键就有主键约束。

6.2.2 主键的特点

1）非空：必须要有值
2）唯一：同一张表中不能出现重复数据
3）一张表最多只能有一个主键

6.2.3 主键列的选择

通常不建议使用与业务相关的字段做为主键，如：身份证、手机号等。往往会单独创建一个字段来做为主键，主键应该是没有含义。主键是给程序员编程使用的，不是给最终用户使用。

6.2.4 创建主键方式

1）在创建表的时候添加主键：

字段名 字段类型 PRIMARY KEY

create table t1(
	id int primary key,				-- 指定id列为主键
    city varchar(20)
);

insert into t1 values(1,'四川成都');

-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 违反主键约束
insert into t1 values(1,'浙江杭州');

2）在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

alter table t1 add primary key(id);

6.2.5 删除主键

-- 语法:
alter table 表名 drop primary key;

-- 删除t1表的主键约束
alter table t1 drop primary key;

-- 查看表详情
desc t1;

-- 给t1表的id列添加一个主键约束
alter table t1 add primary key (id);

-- 查看表详情
desc t1;

6.2.6 主键自增

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值，在最大值加1做为主键值。自增长必须是整数类型，而且必须是主键可以使用。

主键自增的语法：

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

create table t2(
	id int primary key auto_increment,		-- 主键并且自增
    city varchar(20)
);

-- 查看表详情
desc t2;

-- 插入数据
-- 错误
insert into t2 values('黑龙江哈尔滨');

-- 执行几次
insert into t2 (city) values('云南昆明');
insert into t2 (city) values('甘肃兰州');
insert into t2 (city) values('青海西宁');
insert into t2 (city) values('山西太原');
insert into t2 (city) values('辽宁沈阳');
insert into t2 (city) values('内蒙古呼和浩特');

select * from t2;

修改主键自增的起始值

-- 将主键的起始值设置为100

alter table t2 auto_increment = 100;


insert into t2(city) values('山东济南');

-- 第二种写法
insert into t2 values(NULL,'吉林长春');

select * from t2;

6.2.7 联合主键

主键可以不只一个字段，如果有多个字段组成的主键，称为联合主键。

示例：

create table test(
	id int,
	id2 int,
	city varchar(20),
	primary key(id,id2)			-- id和id2列组合为联合主键
);

insert into test values(1,1,'北京');
insert into test values(1,2,'天津');
insert into test values(1,1,'重庆');			-- 联合主键冲突

6.3 唯一约束

概念：这一列的值不能重复

语法：

字段名 字段类型 UNIQUE

添加实现唯一约束

-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

create table t3 (
	id int primary key auto_increment,
	city varchar(20) unique
);

-- 添加一个同名的学生
insert into t3 (city) values('河北石家庄');

-- ERROR 1062 (23000): Duplicate entry '河北石家庄' for key 'city'
insert into t3 (city) values('河北石家庄');

desc t3;

select * from t3;

6.4 非空约束

概念：这一列的值必须输入，不能为空

语法：

字段名 字段类型 NOT NULL

create table t4(
	id int,
	province varchar(30),
	city varchar(20) not null			-- 非空约束
);

insert into t4 values(1,'江苏','南京');

-- ERROR 1048 (23000): Column 'city' cannot be null		city列不能为空
insert into t4 values(2,'江苏',null);

-- ERROR 1364 (HY000): Field 'city' doesn't have a default value	city列没有默认值
insert into t4(id,province) values(2,'江苏');

6.5 默认值

概念：如果某一列没有输入值，使用默认值。如果输入了，则使用输入的值。

语法：

字段名 字段类型 default 默认值

create table t5(
	id int,
	province varchar(20),
	city varchar(20) default '乌鲁木齐'			-- 如果该列没填,默认为乌鲁木齐
);

-- 如果填了以真实的值为准
insert into t5 values(1,'西藏','拉萨');

insert into t5 values(2,'新疆');			-- 报错,不能直接不填city列


-- 可以使用MySQL提供的default关键字
insert into t5 values(3,'新疆',default);

-- 没有填默认为'乌鲁木齐'
insert into t5(id,province) values(2,'新疆');

-- 查询表数据
select * from t5;

6.6 检查约束

检查约束可以使用一定的范围条件来约束我们的列的值，例如年龄应该在0~120岁之间，性别只能有男或女等；

语法：

create table t6(
	id int,
	name varchar(30),
	age int check(age>0 and age<120),			-- 年龄只在0~120之间
	sex char(1) check('男' or '女') 			-- 性别只能在'男' 和 '女'之间选择
);

-- 数据合格
insert into t6 values(null,'小明',20,'男');


-- 违反年龄的检查约束
insert into t6 values(null,'小黄',0,'男');

-- 违反年龄的检查约束
insert into t6 values(null,'小陈',150,'男');

-- 违反性别的检查约束
insert into t6 values(null,'小王',20,'啊');

tips：在MySQL5.7版本不支持检查约束，我们了解即可；

MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-indexes-keys

6.7 外键约束

6.7.1 单表的缺点

创建一个员工表包含如下列(id, name, age, dep_name,dep_location),id主键并自动增长,添加5条数据

CREATE TABLE employee (  
	id INT PRIMARY KEY AUTO_INCREMENT,  
	name VARCHAR(30),  
	age INT,  
	dept_name VARCHAR(30),  
	dept_location VARCHAR(30)  
);  

-- 添加数据  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小明', 25,'研发部', '贵州贵阳');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小龙', 24,'研发部', '贵州贵阳');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小红', 20,'研发部', '贵州贵阳');  

INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小兰', 26,'销售部', '宁夏银川');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小陈', 28,'销售部', '宁夏银川');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小赵', 18,'销售部', '宁夏银川');

以上数据表的缺点:
1. 大量冗余数据出现：研发部、销售部、地址等信息出现了多次
2. 会出现删除异常，如果研发部一个人都没有那么研发部就不存在了

解决方案：

把这一张表拆分成两张表，一张表保存员工，另一张表保存部门。两个表之间通过一个外键建立联系。

-- 主表: 部门表
create table dept(
	id int primary key auto_increment,
	dept_name varchar(20),
	dept_location varchar(20)
);

-- 添加部门信息
insert into dept (dept_name,dept_location) values ('研发部','贵州贵阳'),('销售部', '宁夏银川');

-- 如果存在就把这个表删除
drop table if exists employee;

-- 员工表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int 	-- 外键的数据类型与主表中的主键相同
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);

问题：当我们在employee的dept_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门，实际应用中不能出现这种情况。employee的dept_id中的数据只能是dept表中存在的id

-- 插入3号部门的员工已经可以正常执行
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);

6.7.2 什么是外键约束

部门与员工之间是一对多的关系，一个部门对应多个员工，一个员工属于一个部门。部门是1方，员工是多方。

主表：是一方，部门表
从表：是多方，员工表
什么是外键：外键出现在从表中，被主表的主键约束的那一列外键

6.7.3 创建外键

新建表时增加外键-语法：

[CONSTRAINT `外键名`] FOREIGN KEY(外键字段) REFERENCES 主表(主键)

示例代码：

-- 删除员工表(从表)
drop table employee;

-- 创建员工表(id,name,age,dep_id)

create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int, -- 外键的数据类型与主表中的主键相同
	CONSTRAINT `employee_ibfk_1` foreign key (dept_id) references dept(id)		-- 本表的dept_id列依赖于dept表的id列
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);  

-- 违反外键约束
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);
/*
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db01`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
*/

-- 添加3号部门
insert into dept values(3,'行政部','陕西西安');

-- 再次添加员工并指定为三号部门
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);

select * from employee;

select * from dept;

已有表增加外键-语法：

# 新增:
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键字段) REFERENCES 主表(主键)

示例代码：

-- 添加约束
alter table employee add constraint employee_ibfk_1 foreign key(dept_id) references dept(id);

-- 查看表的建表语句
show create table employee;

6.7.4 删除外键

语法：

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

alter table employee drop foreign key employee_ibfk_1;

-- 查看表的建表语句
show create table employee;

6.7.5 外键的级联

测试数据：

-- 主表: 部门表
create table dept(
	id int primary key auto_increment,
	dept_name varchar(20),
	dept_location varchar(20)
);

-- 添加部门信息
insert into dept (dept_name,dept_location) values ('研发部','贵州贵阳'),('销售部', '宁夏银川');

-- 如果存在就把这个表删除
drop table if exists employee;

-- 员工表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int, 	-- 外键的数据类型与主表中的主键相同
	CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);

1）级联问题引入

出现新的问题：要把部门表中的id值2，改成20，能不能直接更新呢？

update dept set id=20 where id=2; 	-- ERROR 1451 (23000): Cannot delete or update a parent row

当把部门的id修改为20后，原来为2号部门的员工怎么办？

要删除部门id等于1的部门, 能不能直接删除呢？

delete from dept where id=1;  		-- ERROR 1451 (23000): Cannot delete or update a parent row

如果把id为1的部门删除后，原来在1号部门的员工怎么办？

当建立外键约束后，在修改和删除主表的主键时，从表必须设置一定的级联操作；如：随着主表修改而修改，或随着主表的删除而删除；默认情况下，建立外键约束后，MySQL不允许删除或修改主表的主键列；

2）级联的种类

删除级联：
- on delete set null：如果主表有删除，那么从表的数据都为null
- on delete cascade：如果主表有删除，那么从表的数据也删除
- on delete restrict：如果设置该值，主表不允许做删除操作（默认的外键行为）
- on delete no action：即如果存在从数据，不允许删除主数据（和restrict类似）。
修改级联：
- on update set null：如果主表有更新，那么从表的数据都为null
- on update cascade：如果主表有更新，那么从表的数据也更新
- on update restrict：如果设置该值，主表不允许做更新操作（默认的外键行为）
- on update no action：如果从表存在对应数据，不允许更新主表数据（和restrict类似）。

3）级联操作

-- 删除外键约束-- 删除外键约束
alter table employee drop FOREIGN KEY employee_ibfk_1;

-- 添加外键约束，级联更新和级联删除
alter table employee add constraint employee_ibfk_1 foreign key (dept_id) references dept(id) on update cascade on delete cascade;

select * from employee;

select * from dept;

测试修改级联：

mysql> update dept set id=10 where id=1;		# 把部门表中id等于1的部门改成id等于10
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+------+------+---------+
| id | name | age  | dept_id |
+----+------+------+---------+
|  1 | 小明 |   25 |      10 |
|  2 | 小龙 |   24 |      10 |
|  3 | 小红 |   20 |      10 |
|  4 | 小兰 |   26 |       2 |
|  5 | 小陈 |   28 |       2 |
|  6 | 小赵 |   18 |       2 |
+----+------+------+---------+
6 rows in set (0.00 sec)

mysql>

测试删除级联：

mysql> delete from dept where id=2;				-- 删除部门号是2的部门
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;				
+----+------+------+---------+
| id | name | age  | dept_id |
+----+------+------+---------+
|  1 | 小明 |   25 |      10 |
|  2 | 小龙 |   24 |      10 |
|  3 | 小红 |   20 |      10 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql>

6.8 数据约束小结

约束名	关键字	说明
主键	primary key	唯一，非空
默认	default	没有输入值，使用默认值
非空	not null	必须输入
唯一	unique	不能重复
外键	foregin key (外键) references 主表(主键)	外键在从表主表：1方从表：多方

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/131705.html

06【数据库的约束】

文章目录

06【数据库的约束】

6.1 数据库约束的概述

6.1.1 约束的作用：

6.1.2 约束种类：

6.2 主键约束

6.2.1 主键的作用

6.2.2 主键的特点

6.2.3 主键列的选择

6.2.4 创建主键方式

6.2.5 删除主键

6.2.6 主键自增

6.2.7 联合主键

6.3 唯一约束

6.4 非空约束

6.5 默认值

6.6 检查约束

6.7 外键约束

6.7.1 单表的缺点

6.7.2 什么是外键约束

6.7.3 创建外键

6.7.4 删除外键

6.7.5 外键的级联

1）级联问题引入

2）级联的种类

3）级联操作

6.8 数据约束小结

发表回复

06【数据库的约束】

文章目录

06【数据库的约束】

6.1 数据库约束的概述

6.1.1 约束的作用：

6.1.2 约束种类：

6.2 主键约束

6.2.1 主键的作用

6.2.2 主键的特点

6.2.3 主键列的选择

6.2.4 创建主键方式

6.2.5 删除主键

6.2.6 主键自增

6.2.7 联合主键

6.3 唯一约束

6.4 非空约束

6.5 默认值

6.6 检查约束

6.7 外键约束

6.7.1 单表的缺点

6.7.2 什么是外键约束

6.7.3 创建外键

6.7.4 删除外键

6.7.5 外键的级联

1）级联问题引入

2）级联的种类

3）级联操作

6.8 数据约束小结

相关推荐

发表回复