深入浅析MySQL中常见的6种约束类型

概述深入浅析MySQL中常见的6种约束类型

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

NOT NulL ： 确保列不能有NulL值CHECK ： 确保列中的值满足特定条件UNIQUE ： 确保一列中的所有值都不同PRIMARY KEY： NOT NulL和UNIQUE组合，唯一标识表中的每一行FOREIGN KEY ： 外键约束DEFAulT ： 如果未指定值，则为列设置默认值

【相关推荐：mysql视频教程】

约束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(255) not 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(11) check(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(255) check (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: 0MysqL> insert user values(5);query OK, 1 row affected (0.01 sec)

但是，这样的写法你见过吗？

猜猜下面的作用是什么。

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

CREATE table `user` (`age` int(11) CHECK (((case when (`age` >=18) then 1 else (case when age<10 and age >0 then 1 else 2 end) end) =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: 0MysqL> 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(255) DEFAulT 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 INDEX或ALTER table语句：

MysqL> DROP INDEX name_un ON user;query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> show create table user;+-------+-----------------------------------------------------------------------------------------------------------------------------+| table | Create table                                                                                                                |+-------+-----------------------------------------------------------------------------------------------------------------------------+| user  | CREATE table `user` (  `name` varchar(255) DEFAulT 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也相继更新。

RESTRICT：如果子表中有记录，则拒绝更新或删除父表中的记录。

CASCADE：更新或删除父表中的记录时，自动更新或删除子表中的记录。

SET NulL：在更新或删除父表记录时，将子表中字段的值设置为空。

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

MysqL> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userID`) REFERENCES `user` (`ID`) on update cascade ondelete 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: 0MysqL> 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(11) default 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)

原文地址：https://juejin.cn/post/7000352993572814885

作者：i听风逝夜

更多编程相关知识，请访问：编程视频！！ 总结

以上是内存溢出为你收集整理的深入浅析MySQL中常见的6种约束类型全部内容，希望文章能够帮你解决深入浅析MySQL中常见的6种约束类型所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。