MySQL修炼七、约束

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

前言

约束的字面意思就是规定或者限制某个事该如何去做,在MySQL中,约束就是对数据表中数据指定规则,也就是对数据进行限制,以此来确保可靠性,比如不允许某列出现Null值,实际中我们会遇到以下类型的约束。

  • NOT NULL :确保列不能有NULL值
  • CHECK :确保列中的值满足特定条件
  • UNIQUE :确保一列中的所有值都不同
  • PRIMARY KEYNOT NULLUNIQUE组合,唯一标识表中的每一行
  • FOREIGN KEY :外键约束
  • DEFAULT :如果未指定值,则为列设置默认值

约束

1.NULL

MySQL中通过使用NOT NULL确保列中不会出现Null值,创建表时候格式如下:

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)

如果试图插入一个null值,则会抛出异常。

mysql> insert user values(null);
ERROR 1048 (23000): Column 'name' cannot be null

或者在现有表上新增NOT NULL约束。

mysql> alter table user modify name varchar(255not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除NOT NULL约束。

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.CHECK

如果想在列上定义条件约束,可以使用CHECK,比如下面,强制让年龄字段大于18,小于80,否则将会报错。

mysql> create table user(age int(11check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)

插入测试,可以发现9、81在插入的时候抛出异常。

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> 

也可以进行多列约束,如年龄必须大于18,城市必须为中国。

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city='中国'));
Query OK, 0 rows affected, 1 warning (0.05 sec)

插入测试。

mysql> insert user values(81,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(8,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(20,'2');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(20,'中国');
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,'中国1');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values(85,'中国');
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,'中国');
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

还可以让列值必须在指定集合中,如性别必须在男、女、未知、人妖集合中。

mysql> create table user(sex varchar(255check (sex in ('男','女','未知','人妖')));
Query OK, 0 rows affected (0.05 sec)

插入测试。

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)

为约束命名并删除约束。

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint 'CHK_AGE' is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)

但是,这样的写法你见过吗?

猜猜下面的作用是什么。

这其实是一个case when条件判断,让其仅仅可以插入>=18,或者是在0-10之间的数。

CREATE TABLE `user` (`age` int(11CHECK 
(((case when (`age` >=18then 1 
else 
(case when age<10 and age >0 then 1 else 2 endend) =1)));

3.UNIQUE

UNIQUE约束确保列中的没有重复的值,UNIQUE和 PRIMARY KEY约束都为一列值的唯一性提供保障,但是UNIQUE每个表可以出现多次,而PRIMARY KEY只能出现一个。

如下面name字段不能重复。

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)

插入测试。

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name'
mysql> 

对此约束进行起名,并删除。

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name_un'
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

插入后可以用以下语句查看创建语句。

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
user  | CREATE TABLE `user` (
  `name` varchar(255DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

要删除UNIQUE约束,可以使用DROP INDEXALTER TABLE语句:

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
user  | CREATE TABLE `user` (
  `name` varchar(255DEFAULT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在现有表上添加。

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.PRIMARY KEY

通常每个表中包含一个用于唯一标识每一行的值,这个列就被称为PRIMARY KEY。

mysql> create table user (id int(11) ,age int(11),primary key (id));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert user values(1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
mysql> 

5.FOREIGN KEY

FOREIGN KEY用于约束表中的一个字段必须是另一个表中某个字段所存在的值,但是在另一个表中,这个列不一定是主键,但必须是唯一性索引,否则会创建失败。

比如orders表中的userId必须参考user表中的id,如果插入的userId在user表中不存在,则无法插入。

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)

但是存在一个问题,如果主表(user)中记录被删除或者更新,那orders中的记录该怎么办?,如下面的例子,可以发现直接报错了。

mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`REFERENCES `user` (`id`)

MySQL提供了几个约束可以帮助我们解决这类问题,比如在user表更新时,orders也相继更新。

  1. RESTRICT:如果子表中有记录,则拒绝更新或删除父表中的记录。

  2. CASCADE:更新或删除父表中的记录时,自动更新或删除子表中的记录。

  3. SET NULL:在更新或删除父表记录时,将子表中字段的值设置为空。

可以发现,默认采用的是RESTRICT,下面来修改一下,让在更新时候也同样更新,在删除时候设置null。

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`REFERENCES `user` (`id`on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

测试更新

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)


测试删除。

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

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)

6.DEFAULT

DEFAULT约束用于为列设置默认值,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值,没有赋值指的是在insert插入数据时没有指明这个字段,如果指定null值,最终存放的还是null值。

mysql> create table user(age int(11default 18);
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values();
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+------+
| age  |
+------+
|   18 |
+------+
1 row in set (0.00 sec)

原文始发于微信公众号(十四个字节):MySQL修炼七、约束