当前位置：网站首页>数据库系统原理与应用教程（030）—— MySQL 的数据完整性（三）：定义 UNIQUE 约束
数据库系统原理与应用教程（030）—— MySQL 的数据完整性（三）：定义 UNIQUE 约束
2022-07-20 01:43:00 【睿思达DBA_WGX】
数据库系统原理与应用教程（030）—— MySQL 的数据完整性（三）：定义 UNIQUE 约束
目录
定义了 UNIQUE 约束的列不能包含重复值，可以为一个或多个列定义 UNIQUE 约束。UNIQUE 即可以在列级也可以在表级定义，在 UNIQUE 约束的列上可以包含空值。
当需要限定某个表字段的取值唯一、没有重复值时使用 UNIQUE 约束，例如：注册邮箱时的邮箱名、手机号等信息。
一、UNIQUE 约束与主键约束的关系
1、UNIQUE 约束所包含的列允许取空值，主键约束包含的列不允许取空值。
2、UNIQUE 约束强制在指定的列上创建一个唯一索引。
3、一个表最多只有一个主键，但可以有多个 UNIQUE 约束。
4、主键 = UNIQUE 约束 + NOT NULL 。
二、创建表同时创建 UNIQUE 约束
创建 UNIQUE 约束的语法如下：
-- 该方法不能指定约束名。可以使用 “show index from 表名; ” 命令查看索引名。
create table 表名 (
列名 类型 ... ,
列名 类型 unique,
....
);
-- 该方法可以指定约束名。可以使用 “show index from 表名; ” 命令查看索引名。
create table 表名 (
列名 类型 ... ,
[constraint 约束名] unique(列名)
);
例如：
（1）采用第一种格式创建 unique 约束
/* create table t41( id int primary key, name char(20), phone char(20) unique, email varchar(100) unique ); */
mysql> create table t41(
-> id int primary key,
-> name char(20),
-> phone char(20) unique,
-> email varchar(100) unique
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> desc t41;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | UNI | NULL | |
| email | varchar(100) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> show index from t41;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+------------+
| t41 | 0 | PRIMARY | 1 | id | A | 0 |
| t41 | 0 | phone | 1 | phone | A | 0 |
| t41 | 0 | email | 1 | email | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
3 rows in set (0.14 sec)
（2）采用第一种格式创建 unique 约束
/* create table t42( id int primary key, name char(20), phone char(20), email varchar(100), constraint uq_phone unique(phone), unique(email) ); */
mysql> create table t42(
-> id int primary key,
-> name char(20),
-> phone char(20),
-> email varchar(100),
-> constraint uq_phone unique(phone),
-> unique(email)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc t42;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | UNI | NULL | |
| email | varchar(100) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> show index from t42;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
| t42 | 0 | PRIMARY | 1 | id | A | 0 |
| t42 | 0 | uq_phone | 1 | phone | A | 0 |
| t42 | 0 | email | 1 | email | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
3 rows in set (0.02 sec)
三、为已有的表添加 UNIQUE 约束
创建表之后可以添加 UNIQUE 约束，语法格式如下：
-- 添加列同时创建 UNIQUE 约束
alter table 表名 add 列名 <类型> unique;
-- UNIQUE 约束包含的列已存在
alter table 表名 add [constraint 约束名] unique(列名);
例如：
（1）添加列同时定义为 UNIQUE 约束
/* create table t43( id int primary key, name char(20), phone char(20) ); */
mysql> create table t43(
-> id int primary key,
-> name char(20),
-> phone char(20)
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> desc t43;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> alter table t43 add email varchar(50) unique;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t43;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show index from t43;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
| t43 | 0 | PRIMARY | 1 | id | A | 0 |
| t43 | 0 | email | 1 | email | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
2 rows in set (0.00 sec)
（2）为已有的列定义 UNIQUE 约束
mysql> desc t43;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table t43 add constraint uq_phone unique(phone);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t43;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | UNI | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show index from t43;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
| t43 | 0 | PRIMARY | 1 | id | A | 0 |
| t43 | 0 | email | 1 | email | A | 0 |
| t43 | 0 | uq_phone | 1 | phone | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+-
3 rows in set (0.00 sec)
四、删除 UNIQUE 约束
删除 UNIQUE 约束之前可以使用 【show index from 表名; 】查看 UNIQUE 约束的名称。删除 UNIQUE 约束的命令格式如下：
alter table 表名 drop index 约束名;
例如：删除 t43 表中的 UNIQUE 约束
（1）查看约束名
mysql> show index from t43;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
| t43 | 0 | PRIMARY | 1 | id | A | 0 |
| t43 | 0 | email | 1 | email | A | 0 |
| t43 | 0 | uq_phone | 1 | phone | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+-
3 rows in set (0.00 sec)
（2）删除 UNIQUE 约束
mysql> alter table t43 drop index uq_phone;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t43 drop index email;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t43;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from t43;
+-------+------------+----------+--------------+-------------+-----------+-------------+-
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
| t43 | 0 | PRIMARY | 1 | id | A | 0 |
|+-------+------------+----------+--------------+-------------+-----------+-------------+
1 row in set (0.00 sec)
边栏推荐
- The way of practicing and fighting weird: the difference between NPM and yarn
- [solve ax=b]
- ORA-39194: Table mode jobs require the tables to be comma separated.
- 音视频入门——H.264编码（宏块+片+帧）浅析
- 详解Redis的RDB和AOF
- 【Pygame小游戏】魂斗罗经典BOSS都回来了 准备好再次击败他们了吗？（附源码）
- 数据库系统原理与应用教程（026）—— MySQL 修改表中数据（二）：删（delete from）
- uview錶單，身份證，鍵盤，實時驗證
- 网络安全知识图谱关键技术
- 百度飞桨EasyDL助力制造企业智能化转型
猜你喜欢
随机推荐
Check excellent Tkinter open source contributors [pypi]
The way of practicing and fighting weird: the difference between NPM and yarn
Unity shader shader learning (I)
VMware 启动报错：Exception 0xc0000005和windwos11没有Hyper-V的解决方法
统计代码耗时的一个不常用方法
实习打怪之路：npm和yarn的区别
[story proof and probability axiom]
【求解AX=b】
数据库系统原理与应用教程（032）—— MySQL 的数据完整性（五）：定义自增列（AUTO_INCREMENT）
Unity Shader着色器学习（一）
Check code online calculation tool
实习打怪之路：nodejs中的npm全局安装和局部安装的意思，npm中的全局安装和局部安装的区别
DNS domain name resolution
数字化知识管理理论与应用研究综述
Bubble sort and quick sort
Xshell & putty color scheme
Build product array
Frontier and application of data security technology from the perspective of compliance
AVL tree
Translate official UE documents about uobject Foundation