MySQL——约束

最新推荐文章于 2024-05-11 16:15:20 发布

前丨尘忆·梦

最新推荐文章于 2024-05-11 16:15:20 发布

阅读量374

点赞数

分类专栏： MySQL数据库基础+高级文章标签： mysql dba 数据库

本文链接：https://blog.csdn.net/qq_36879493/article/details/121756753

版权

MySQL数据库基础+高级专栏收录该内容

26 篇文章 2 订阅

订阅专栏

约束

1、什么是约束？

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

约束的作用就是为了保证：表中的数据有效！！

2、约束包括哪些？

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

2.1、非空约束：not null

非空约束 not null 约束的字段不能为 NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null  -- not null只有列级约束，没有表级约束！
);

mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name) values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

2.2、唯一性约束: unique

唯一性约束 unique 约束的字段不能重复，但是可以为 NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) unique, -- 约束添加在列的后面，这种约束被称为列级约束
	email varchar(255)
);

mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
+------+----------+------------------+
3 rows in set (0.00 sec)

mysql> insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
mysql> insert into t_vip(id) values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id) values(5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+
5 rows in set (0.00 sec)

新需求：name 和 email 两个字段联合起来具有唯一性！！！！

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	unique(name,email) -- 约束没有添加在列的后面，这种约束被称为表级约束。
);

mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+------+----------+-------------------+
| id   | name     | email             |
+------+----------+-------------------+
|    1 | zhangsan | zhangsan@123.com  |
|    2 | zhangsan | zhangsan@sina.com |
+------+----------+-------------------+
2 rows in set (0.00 sec)

mysql> insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

什么时候使用表级约束呢？

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

unique 和 not null 可以联合吗？

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null unique
);

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

在 mysql 当中，如果一个字段同时被 not null 和 unique 约束的话，该字段自动变成主键字段。（注意：oracle 中不一样！）

mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name) values(2,'zhangsan'); -- 错误了：name不能重复
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

mysql> insert into t_vip(id) values(2); -- 错误了：name不能为NULL
ERROR 1364 (HY000): Field 'name' doesn't have a default value

2.3、主键约束（primary key，简称 PK）

主键约束的相关术语？

主键约束：就是一种约束
主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
主键值：主键字段中的每一个值都叫做：主键值

什么是主键？有啥用？

主键值是每一行记录的唯一标识
主键值是每一行记录的身份证号！！！

记住：任何一张表都应该有主键，没有主键，表无效！！

主键的特征：not null + unique（主键值不能是 NULL，同时也不能重复！）

怎么给一张表添加主键约束呢？

drop table if exists t_vip;
-- 1个字段做主键，叫做：单一主键
create table t_vip(
	id int primary key, -- 列级约束
	name varchar(255)
);

mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name) values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into t_vip(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

可以这样添加主键吗，使用表级约束？

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255),
	primary key(id) -- 表级约束
);

mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(id,name) values(1,'lisi');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

表级约束主要是给多个字段联合起来添加约束：

drop table if exists t_vip;
-- id和name联合起来做主键：复合主键！！！！
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	primary key(id,name)
);

在实际开发中不建议使用：复合主键。建议使用单一主键！因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用！！！

主键值建议使用：

bigint
int
char 等类型

不建议使用：varchar 来做主键。主键值一般都是数字，一般都是定长的！

2.3.1、自然主键和业务主键

主键除了：单一主键和复合主键之外，还可以这样进行分类？

自然主键：主键值是一个自然数，和业务没关系！
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

在 mysql 当中，有一种机制，可以帮助我们自动维护一个主键值？

drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment, -- auto_increment表示自增，从1开始，以1递增！
	name varchar(255)
);

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
|  4 | zhangsan |
|  5 | zhangsan |
|  6 | zhangsan |
|  7 | zhangsan |
+----+----------+
7 rows in set (0.00 sec)

2.4、外键约束：foreign key,让表于表产生关系，从而保证数据的正确性

先看一个场景：

-- 外键约束：foreign key,让表于表产生关系，从而保证数据的正确性
CREATE TABLE emp_fk (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_name VARCHAR(30), -- 部门名称
	dep_location VARCHAR(30) -- 部门地址
);

-- 添加数据
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp_fk (NAME, age, dep_name, dep_location)
VALUES ('小王', 18, '销售部', '深圳');

mysql> select * from emp_fk;
+----+--------+------+-----------+--------------+
| id | NAME   | age  | dep_name  | dep_location |
+----+--------+------+-----------+--------------+
|  1 | 张三   |   20 | 研发部    | 广州         |
|  2 | 李四   |   21 | 研发部    | 广州         |
|  3 | 王五   |   20 | 研发部    | 广州         |
|  4 | 老王   |   20 | 销售部    | 深圳         |
|  5 | 大王   |   22 | 销售部    | 深圳         |
|  6 | 小王   |   18 | 销售部    | 深圳         |
+----+--------+------+-----------+--------------+
6 rows in set (0.00 sec)

发现，存在数据冗余现象。即：研发部和广州、销售部和深圳存在重复！

解决问题的办法是把表拆分为两个表：

-- 表的拆分
-- 解决方案：分成 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, '销售部', '深圳');
-- 添加员工,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);

我们再来观察这两个表：

mysql> select * from employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 张三   |   20 |      1 |
|  2 | 李四   |   21 |      1 |
|  3 | 王五   |   20 |      1 |
|  4 | 老王   |   20 |      2 |
|  5 | 大王   |   22 |      2 |
|  6 | 小王   |   18 |      2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    | 广州         |
|  2 | 销售部    | 深圳         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

还存在一个问题：如果我将 department 表中的一个部门删除，那么 employee 表中就会出现存储数据错误的现象。

如何避免这样的现象：使用外键约束，把主表列和从表的外键列进行 “绑定”，产生关系，从而避免这样的情况发生。

2.4.1、创建表的时候，添加外键

create table 表名(
    ....
    外键列
    constraint 外键名称 foreign key (外键列名称)
    references 主表名称(主表列名称)
);

create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int, -- 外键对应主表的主键
	CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES
	department(id)
);

-- 添加 2 个部门
insert into department values(null, '研发部','广州'),
(null, '销售部', '深圳');
-- 添加员工,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);

department 表是主表，employee 是从表。

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    | 广州         |
|  2 | 销售部    | 深圳         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 张三   |   20 |      1 |
|  2 | 李四   |   21 |      1 |
|  3 | 王五   |   20 |      1 |
|  4 | 老王   |   20 |      2 |
|  5 | 大王   |   22 |      2 |
|  6 | 小王   |   18 |      2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)

如果此时我想删除 department 的其中一个记录，结果如何？

mysql> delete from department where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`laodu_mysql`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

提示：有外键约束，无法删除！

那如果我想添加一个 dep_id = 5 的员工信息呢？

mysql> insert into employee values(7, 'wangzha', 20, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`laodu_mysql`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

同样，无法更新！

2.4.2、删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

2.4.3、创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

2.4.5、级联操作

分类
- 级联更新：ON UPDATE CASCADE
- 级联删除：ON DELETE CASCADE

添加级联操作

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

在这里插入图片描述

-- 先删除
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 添加级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    | 广州         |
|  2 | 销售部    | 深圳         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 张三   |   20 |      1 |
|  2 | 李四   |   21 |      1 |
|  3 | 王五   |   20 |      1 |
|  4 | 老王   |   20 |      2 |
|  5 | 大王   |   22 |      2 |
|  6 | 小王   |   18 |      2 |
+----+--------+------+--------+
6 rows in set (0.01 sec)

我们把 department 表中的 “研发部” 部门 id 改为 5：

mysql> update department set id = 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  2 | 销售部    | 深圳         |
|  5 | 研发部    | 广州         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

发现已经修改成功，那么 employee 表中原先 dep_id 为 1 的数据会发生什么变化？

mysql> select * from employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 张三   |   20 |      5 |
|  2 | 李四   |   21 |      5 |
|  3 | 王五   |   20 |      5 |
|  4 | 老王   |   20 |      2 |
|  5 | 大王   |   22 |      2 |
|  6 | 小王   |   18 |      2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)

全部发生了更新！

-- 先删除
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 添加级联删除和级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

这次我把 department 表中 dep_id 为 2 的删除：

mysql> delete from department where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  5 | 研发部    | 广州         |
+----+-----------+--------------+
1 row in set (0.00 sec)

删除成功！同样，employee 表中原先 dep_id 为 2 的数据会发生什么变化？

mysql> select * from employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 张三   |   20 |      5 |
|  2 | 李四   |   21 |      5 |
|  3 | 王五   |   20 |      5 |
+----+--------+------+--------+
3 rows in set (0.12 sec)

可见，也全部被删除！

前丨尘忆·梦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL——约束

约束1、什么是约束？在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！约束的作用就是为了保证：表中的数据有效！！2、约束包括哪些？非空约束：not null唯一性约束: unique主键约束: primary key （简称PK）外键约束：foreign key（简称FK）检查约束：check（mysql不支持，oracle支持）2.1、非空约束：not null非空约束 not null 约束的字段不能为 NULL。drop table
复制链接

扫一扫