mysql的五种约束

什么是约束？

设计一个数据库，需要一种方法来保证只在表中插入合法的数据，管理如何插入或处理数据的规则就是约束 。 约束有五种不同的种类， 类型见下表

约束类型非空约束唯一约束默认值约束检查约束主键约束外键约束关键词NOT NULLUNIQUEDEFAULTCHECKPRIMARY KEYFOREIGN KEY

一 非空约束

从简单的开始说起，not null, 听名字就能理解，被非空约束的列，在插入时必须给定值 ，否则报错 。 创建一个学生表student1，给student_name添加非空约束 ，

mysql> create table student1 -> ( -> student_id varchar(10) , -> student_name varchar(10) not null -> ) ; Query OK, 0 rows affected (0.12 sec)

只插入student_id，不添加student_name ，报错

mysql> insert into student1(student_id) values( '001'); ERROR 1364 (HY000): Field 'student_name' doesn't have a default value

二 唯一约束

unique约束的字段， 具有唯一性，不可重复 ，但可以为空 。

创建一个学生表student2a，给student_id添加唯一约束 ，

mysql> create table student2a -> ( -> student_id varchar(10) unique , -> student_name varchar(10) -> ) ; Query OK, 0 rows affected (0.14 sec)

添加两条 student_id 一样的数据

mysql> insert into student2a values( '001', "张三"); Query OK, 1 row affected (0.06 sec) mysql> insert into student2a values( '001', "张三"); ERROR 1062 (23000): Duplicate entry '001' for key 'student_id'

Duplicate 重复啦， 不可以！

联合唯一约束

使用表级约束建立约束，

表级约束 ，对表中多个列作用 列级约束， 对表中单个列作用

创建一个学生表student2b，给（student_id ， student_name）共同添加唯一约束 ，

mysql> create table student2b -> ( -> student_id varchar(10) , -> student_name varchar(10) , -> unique(student_id, student_name) -> ); Query OK, 0 rows affected (0.08 sec)

尝试添加 student_id， student_name 都一样的数据

mysql> insert into student2b values( '001', "张三"); ERROR 1062 (23000): Duplicate entry '001-张三' for key 'student_id' mysql> insert into student2b values( '001', "张三"); ERROR 1062 (23000): Duplicate entry '001-张三' for key 'student_id' mysql> insert into student2b values( '002', "张三"); Query OK, 1 row affected (0.02 sec) mysql> select * from student2b; +------------+--------------+ | student_id | student_name | +------------+--------------+ | 001 | 张三 | | 002 | 张三 | +------------+--------------+ 2 rows in set (0.00 sec)

说明两个加起来不重复可以添加 。

三 默认值约束

默认约束可以default指定默认值，不传值就使用默认，就像函数里的默认值参数，不传值就使用默认。 创建一个学生表student3，其中student_class默认值设为“一班”，

mysql> create table student3 -> ( -> student_id varchar(10) , -> student_name varchar(10) , -> unique(student_id, student_name) , -> student_class varchar(20) default '一班' -> ); Query OK, 0 rows affected (0.08 sec)

在导入一条数据时， 不给student_class传值，

mysql> insert into student3 (student_id, student_name) values( '001', "张三"); Query OK, 1 row affected (0.10 sec) mysql> select * from student3; +------------+--------------+---------------+ | student_id | student_name | student_class | +------------+--------------+---------------+ | 001 | 张三 | 一班 | +------------+--------------+---------------+ 1 row in set (0.00 sec)

插入结果为默认值。

四 检查约束

检查约束CHECK用来保证一列的插入数据满足指定的条件 ，不满足条件不可插入，返回错误。常用用途如下：

检查最大值或者最小值，比如学生插入的成绩 在0 ，100之间只允许特定的值，比如学生性别只能是 男或者女

创建一个学生表student4，规定student_sex取值只能是“男”或“女”。

mysql> create table student4 -> ( -> student_id varchar(10) , -> student_name varchar(10) , -> unique(student_id, student_name) , -> student_class varchar(20) default '一班' , -> student_sex varchar(20) check (student_sex ="男" or student_sex ="女" ) -> ) ; Query OK, 0 rows affected (0.06 sec)

传入一个不在规定内的性别 “外星人” ，肯定不符合要求 ，

mysql> insert into student4 values("001", "张三", "一班", " 外星人"); Query OK, 1 row affected (0.01 sec)

为什么没有报错呢，原来在官网文档中有这样一句话

The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”.

即CHECK语句会被忽略，也意味着MySQL被没有实现检查约束功能，此时要实现检查约束的功能， 可以参考¹ 。 但在mysql 8.0.16实现了这个功能，如果你是最新的版本，就可以使用这个功能。

五 主键约束

主键约束是一种特殊的约束，用来保证一列的值是唯一且非空的 ，主键值是该行的唯一标识符 。 换句话说 ，主键值就是该行数据的身份证号 。 没有主键，要安全的更新或者删除数据而不影响其他行将很困难。

表中的某个字段添加主键约束后，该字段为主键字段，主键字段中的每一个数据都称为主键值， 主键特性：

不重复不为空不可改动 （大多数数据库的主键值不允许改动，少量数据库可以，但也千万别这么做。）不能重用， 删除某一行之后，该行的主键值也不能分配给新行 。

创建一个学生表student5，主键为student_id

mysql> create table student5 -> ( -> student_id varchar(10) primary key, -> student_name varchar(10) , -> unique(student_id, student_name) , -> student_class varchar(20) default '一班' , -> student_sex varchar(20) -> ) ; Query OK, 0 rows affected (0.06 sec)

插入重复主键值，是不可以的

mysql> insert into student5 values ("001", "张三", "一班", " 男"); Query OK, 1 row affected (0.02 sec) mysql> insert into student5 values ("001", "李四", "一班", " 女"); ERROR 1062 (23000): Duplicate entry '001' for key 'PRIMARY'

联合主键

联合主键要求加起来不重复， 而且主键都不能空 ，比如学生姓名可以相同，但不能学号、姓名都相同，除非有人顶替你上大学了/ 笑 创建一个学生表student5b，联合主键为student_id 和 student_name

mysql> create table student5b -> ( -> student_id varchar(10) , -> student_name varchar(10) , -> primary key(student_id, student_name) , -> student_class varchar(20) default '一班' , -> student_sex varchar(20) -> ) ; Query OK, 0 rows affected (0.03 sec)

插入数据student_id 相同， student_name不同的数据可以

mysql> insert into student5b values ("001", "张三", "一班", " 男"); Query OK, 1 row affected (0.01 sec) mysql> insert into student5b values ("001", "李四", "一班", " 女"); Query OK, 1 row affected (0.00 sec)

插入数据student_id 和 student_name都相同的数据不可以

mysql> insert into student5b values ("001", "李四", "一班", " 女"); ERROR 1062 (23000): Duplicate entry '001-李四' for key 'PRIMARY'

唯一约束和主键约束的重要区别

表可以有多个唯一约束 ， 只能有一个主键约束唯一约束可以为空值， 主键约束不可以唯一约束可修改或者更新 ， 主键约束不可以唯一约束的值删除后可以重新添加，主键约束不可以唯一约束不可以定义外键约束

6外键约束

外键定义：外键必须是表中的一列， 其值必须来自另一个表的主键 。比如有两个表，学生表和成绩表， 成绩表中的 学生 必须来自学生表中的 学生 。主键所在的表为父表，外键所在的表称为子表 ，子表引用父表 ，不能添加父表没有的， 父表也不能删除子表应用的字段 。（或者称为主表，副表）

注意：外键的数据格式与主键相同（当然了）

创建一个学生表student6，一个成绩表scores, 学生表主键为student_id ，成绩表student_id为外键

mysql> create table student6 -> ( -> student_id varchar(10) primary key, -> student_name varchar(10) , -> unique(student_id, student_name) , -> student_class varchar(20) default '一班' , -> student_sex varchar(20) -> ) ; Query OK, 0 rows affected (0.14 sec) mysql>create table scores -> ( -> student_id varchar(10), -> student_name varchar(10), -> score tinyint not null, -> foreign key(student_id) references student6(student_id) -> );

成绩表中插入学生表中不存在的 student_id, 不允许

mysql> insert into student6 values ("001", "张三", "一班", " 男"); Query OK, 1 row affected (0.00 sec) mysql> insert into scores values ("001", "张三", 85); Query OK, 1 row affected (0.01 sec) mysql> insert into scores values ("002", "李四", 98); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`scores`, CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student6` (`student_id`))

感谢您在茫茫的网络世界中阅读了本文， 希望没有浪费您宝贵的时间，期待您指出文中的不足！

---

检查约束无效怎么办？¹: https://blog.csdn.net/ldx19980108/article/details/79921853 ↩︎ ↩︎