MySQL入门（7）- 约束——非空约束&唯一性约束

最新推荐文章于 2025-11-11 11:30:00 发布

原创最新推荐文章于 2025-11-11 11:30:00 发布 · 4.1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#MYSQL入门 #非空约束 #唯一性约束 #unique

MySQL 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了MySQL中的约束，包括非空约束（not null）和唯一性约束（unique）。非空约束确保字段不为空，而唯一性约束保证字段值的唯一性。通过示例展示了如何在创建表时添加这些约束，并通过插入数据来演示约束的效果。

约束——非空约束&唯一性约束

此系列记录PN视频学习笔记

* 本博客涉及到的数据为bjpowernode.sql，数据见文末，SQL文见MySQL入门（9）文末。

* 该数据有三张表，分别是部门表dept（deptno部门编号、dname部门名称、loc位置）、

员工表emp（empno工号、ename员工姓名、job职位、mgr直属领导工号、

hiredate入职日期、sal月薪、comm补贴、deptno部门编号）、

薪水等级表salgrade（grade等级、losal区间下限、hisal区间上限）

约束（constraint）

1、什么是约束，为什么要使用约束？

-约束对应的英语单词：constraint

-约束实际上就是表中数据的限制条件

-表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效。

2、约束包括哪些？

- 非空约束 (not null)

- 唯一性约束 (unique)

- 主键约束 (primary key) 简称 PK

- 外键约束 (foreign key) 简称 FK

- 检查约束【目前MYSQL不支持，oracle数据库支持】

3、非空约束

-not null 约束的字段，不能为NULL值，必须给定具体的数据，以保证该字段不为空

-创建表，给字段添加非空约束【创建用户表，用户的邮箱地址不能为空】

droptable if exists t_user;

createtable t_user(

idint(10),

namevarchar(32) not null,

emailvarchar(128)

);

mysql> desc t_user;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(10) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

insert into t_user(id,name,email) values(1, 'jack', 'jack@bjpowernode.com');

mysql> select * from t_user;

+------+------+----------------------+

| id | name |email |

+------+------+----------------------+

| 1 | jack | jack@bjpowernode.com|

+------+------+----------------------+

insert into t_user(id,name,email) values(2, 'lisi@bjpowernode.com');

ERROR 1136 (21S01): Column count doesn't match value count atrow 1

4、唯一性约束

-unique约束的字段具有唯一性，不可重复

-创建用户表，保证邮箱地址

【列级约束】字段后加unique

drop tableif exists t_user;

create table t_user(

idint(10),

namevarchar(32) not null,

emailvarchar(128) unique

);

insertinto t_user(id,name,email) values(1, 'jack', 'jack@bjpowernode.com');//成功

mysql>select * from t_user;

+------+------+----------------------+

| id | name |email |

+------+------+----------------------+

| 1 | jack |jack@bjpowernode.com |

+------+------+----------------------+

mysql>insert into t_user(id,name,email) values(2, 'abc', 'jack@bjpowernode.com');//失败

ERROR 1062 (23000): Duplicate entry 'jack@bjpowernode.com' for key 'email'

insert into t_user(id,name) values(2, 'abc');//成功

insert into t_user(id,name) values(3, 'def');//成功

mysql> select * from t_user;

+------+------+----------------------+

| id | name |email |

+------+------+----------------------+

| 1 | jack |jack@bjpowernode.com |

| 2 | abc | NULL |

| 3 | def | NULL |

+------+------+----------------------+

“unique约束”约束的字段不能重复，但是可以为NULL

【表级约束】

droptable if exists t_user;

create table t_user(

idint(10),

namevarchar(32) not null,

emailvarchar(128),

unique(email)

);

可以使用表级约束给多个字段联合添加约束【以下程序表示name和email两个字段联合唯一】

【表级约束】

drop table if exists t_user;

create table t_user(

idint(10),

namevarchar(32) not null,

emailvarchar(128),

unique(name,email)

);

mysql> desc t_user;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(10) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

insert into t_user(id,name,email) values(1, 'abc', 'abc@163.com');//成功

insert into t_user(id,name,email) values(1, 'xxx', 'abc@163.com');//成功

insert into t_user(id,name,email) values(1, 'abc', 'def@163.com');//成功

mysql> select * from t_user;

+------+------+-------------+

| id | name | email |

+------+------+-------------+

| 1 | abc | abc@163.com |

| 1 | xxx | abc@163.com |

| 1 | abc | def@163.com |

+------+------+-------------+

mysql> insert into t_user(id,name,email) values(1, 'abc','abc@163.com');//失败

ERROR 1062 (23000): Duplicate entry 'abc-abc@163.com' for key 'name'

//表级约束还可以给约束起名字

//为什么要起名字？因为以后要通过名字来删除/操作约束。

drop table if exists t_user;

create table t_user(

idint(10),

namevarchar(32) not null,

emailvarchar(128),

constraint t_user_email_unique unique(email)

);

mysql> desc t_user;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(10) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

//查询约束的名字

mysql>show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bjpowernode |

| mydb |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

+--------------------+

mysql> use information_schema;

Database changed

mysql> show tables;

+---------------------------------------+

| Tables_in_information_schema |

+---------------------------------------+

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS |

| COLUMN_PRIVILEGES |

| ENGINES |

| EVENTS |

| FILES |

| GLOBAL_STATUS |

| GLOBAL_VARIABLES |

| KEY_COLUMN_USAGE |

| OPTIMIZER_TRACE |

| PARAMETERS |

| PARTITIONS |

| PLUGINS |

| PROCESSLIST |

| PROFILING |

| REFERENTIAL_CONSTRAINTS |

| ROUTINES |

| SCHEMATA |

| SCHEMA_PRIVILEGES |

| SESSION_STATUS |

| SESSION_VARIABLES |

| STATISTICS |

| TABLES |

| TABLESPACES |

| TABLE_CONSTRAINTS |

| TABLE_PRIVILEGES |

| TRIGGERS |

| USER_PRIVILEGES |

| VIEWS |

| INNODB_LOCKS |

| INNODB_TRX |

| INNODB_SYS_DATAFILES |

| INNODB_FT_CONFIG |

| INNODB_SYS_VIRTUAL |

| INNODB_CMP |

| INNODB_FT_BEING_DELETED |

| INNODB_CMP_RESET |

| INNODB_CMP_PER_INDEX |

| INNODB_CMPMEM_RESET |

| INNODB_FT_DELETED |

| INNODB_BUFFER_PAGE_LRU |

| INNODB_LOCK_WAITS |

| INNODB_TEMP_TABLE_INFO |

| INNODB_SYS_INDEXES |

| INNODB_SYS_TABLES |

| INNODB_SYS_FIELDS |

| INNODB_CMP_PER_INDEX_RESET |

| INNODB_BUFFER_PAGE |

| INNODB_FT_DEFAULT_STOPWORD |

| INNODB_FT_INDEX_TABLE |

| INNODB_FT_INDEX_CACHE |

| INNODB_SYS_TABLESPACES |

| INNODB_METRICS |

| INNODB_SYS_FOREIGN_COLS |

| INNODB_CMPMEM |

| INNODB_BUFFER_POOL_STATS |

| INNODB_SYS_COLUMNS |

| INNODB_SYS_FOREIGN |

| INNODB_SYS_TABLESTATS |

+---------------------------------------+

mysql> desc TABLE_CONSTRAINTS;【TABLE_CONSTRAINTS是专门存储约束信息的表格】

+--------------------+--------------+------+-----+---------+-------+

+--------------------+--------------+------+-----+---------+-------+

+--------------------+--------------+------+-----+---------+-------+

mysql>select constraint_name from table_constraints where table_name = 't_user';

+---------------------+

| constraint_name |

+---------------------+

| t_user_email_unique |

+---------------------+

5、not null 和unique可以联合使用吗？

-可以联合使用

-被not null unique约束的字段，既不能为空，也不能重复

-案例：

droptable if exists t_user;

createtable t_user(

idint(10),

namevarchar(32) not null unique

);

insertinto t_user(id,name) values(1, 'jack');//成功

mysql>select * from t_user;

+------+------+

| id | name |

+------+------+

| 1 | jack |

+------+------+

mysql>insert into t_user(id,name) values(2, 'jack'); //失败

ERROR 1062 (23000): Duplicate entry 'jack' for key 'name'

mysql>insert into t_user(id,name) values(3); //失败

ERROR 1136 (21S01): Column count doesn't match value count at row 1

insertinto t_user(id,name) values(3, 'jackson');//成功

mysql> select * from t_user;

+------+---------+

| id | name |

+------+---------+

| 1 | jack |

| 3 | jackson |

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCHING	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800	NULL	20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975	NULL	20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850	NULL	30
7782	CLARK	MANAGER	7839	1981/6/9	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987/4/19	3000	NULL	20
7839	KING	PERSIDENT	NULL	1981/11/17	5000	NULL	10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	NULL	30
7876	ADAMS	CLERK	7788	1987/5/23	1100	NULL	20
7900	JAMES	CLERK	7698	1981/12/3	950	NULL	30
7902	FORD	ANALYST	7566	1981/12/3	3000	NULL	20
7934	MILLER	CLERK	7782	1982/1/23	1300	NULL	10