vlambda
2022-05-17

约束自己还是约束别人？不！俺说的是MySQL中的约束呢！

约束是什么鬼？来自百科的解释：

约束是一个汉语词语，读音是yuē shù，意思是指有缠缚，束缚，限制，管束，处罚等意思。古有盟约之意，如《战国策·燕策》：“坚明约束。”中约束一词。

“
额。。。好像跑题了，回到正题。
”

MySQL中的约束概述

约束是一种限制，它通过限制表中的数据，来确保数据的完整性和唯一性。

“
例如，在某数据表中有一个存储金额的字段，如果随便输入字符串类型的数据，那这些无效的值就毫无意义了。
”

MySQL支持以下约束：

1. 主键约束

2. 外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。例如，有一个汽车店，只有宝马、奔驰、本田、丰田4个品牌，那么，你来到这个汽车店买车，就只能选择这4个品牌，不能购买其它的品牌。

3. 唯一约束

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

例如，在主机信息表中，要避免表中的主机名重名，就可以把主机名列设置为唯一约束。

4. 检查约束

检查约束是用来检查数据表中，字段值是否有效的一个手段。

5. 非空约束

非空约束用来约束表中的字段不能为空。例如，在主机表中，如果不添加主机名，那么这条记录是没有用的。

6. 默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

例如，将主机录入CMDB信息时，如果不输入主机名叫啥，那么会默认设置一个主机名（比如host001）或者输入一个“localhost”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

“
以上 6 种约束中，一个数据表中只能有一个主键约束，其它约束可以有多个。
”

主键约束

1. 设置单字段主键

# 创建表，将hostid设置为主键
create table hosts (
hostid BIGINT UNSIGNED PRIMARY KEY,
name VARCHAR(64),
status INT DEFAULT 0
);
# 查看表结构
mysql> desc hosts;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | NO   | PRI | NULL    |       |
| name   | varchar(64)     | YES  |     | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

定义主键的另外一种方式，如下：

# 创建表，将hostid设置为主键
create table hosts_new (
hostid BIGINT UNSIGNED,
name VARCHAR(64),
status INT DEFAULT 0,
PRIMARY KEY(hostid) # 此处定义主键
);

# 查看
mysql> desc hosts_new;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | NO   | PRI | NULL    |       |
| name   | varchar(64)     | YES  |     | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

2. 在创建表时设置联合主键

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

“
注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
”

创建数据表 hosts，假设表中没有主键 id，为了唯一确定一台主机，可以把 name、hostid联合起来作为主键

mysql> create table hosts(
    -> hostid BIGINT UNSIGNED,
    -> name VARCHAR(64),
    -> status INT DEFAULT 0,
    -> PRIMARY KEY(hostid,name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc hosts;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | NO   | PRI | NULL    |       |
| name   | varchar(64)     | NO   | PRI | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

3. 在修改表时添加主键约束

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值。

语法：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

修改hosts表，将hostid设置为主键

# 先查看一个表，此表没有主键
mysql> desc hosts;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | YES  |     | NULL    |       |
| name   | varchar(64)     | YES  |     | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 修改表，添加主键
mysql> alter table hosts add PRIMARY KEY(hostid); 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 再次查看
mysql> desc hosts;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | NO   | PRI | NULL    |       |
| name   | varchar(64)     | YES  |     | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

4. 删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

mysql> alter table hosts drop PRIMARY KEY;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc hosts;
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| hostid | bigint unsigned | NO   |     | NULL    |       |
| name   | varchar(64)     | YES  |     | NULL    |       |
| status | int             | YES  |     | 0       |       |
+--------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

“
由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。
”

5. 主键自增长

在MySQL中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长

案例

mysql> create table hosts (
    -> hostid BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(64),
    -> status INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc hosts;
+--------+-----------------+------+-----+---------+----------------+
| Field  | Type            | Null | Key | Default | Extra          |
+--------+-----------------+------+-----+---------+----------------+
| hostid | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(64)     | YES  |     | NULL    |                |
| status | int             | YES  |     | 0       |                |
+--------+-----------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into hosts(name,status) values('pc01',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into hosts(name,status) values('pc02',0); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into hosts(name,status) values('pc03',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into hosts(name,status) values('pc10',1);  
Query OK, 1 row affected (0.00 sec)

mysql> insert into hosts(name,status) values('pc12',0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from hosts;
+--------+------+--------+
| hostid | name | status |
+--------+------+--------+
|      1 | pc01 |      0 |
|      2 | pc02 |      0 |
|      3 | pc03 |      1 |
|      4 | pc10 |      1 |
|      5 | pc12 |      0 |
+--------+------+--------+
5 rows in set (0.00 sec)

mysql>

外键约束

1. 在创建表时设置外键约束

案例：创建interface表，并在表 interface 上创建外键约束，约束名称为c_interface_1，让它的键 hostid 作为外键关联到表hosts的主键hostid

# 创建hosts表
mysql> create table hosts (
    -> hostid int(4) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(64),
    -> status INT DEFAULT 0
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)


# 创建interface表，并在表 interface 上创建外键约束，约束名称为c_interface_1，让它的键 hostid 作为外键关联到表 hosts 的主键hostid
mysql> create table interface (
    -> interfaceid BIGINT UNSIGNED,
    -> ip VARCHAR(64) DEFAULT '127.0.0.1',
    -> hostid int(4),
    -> CONSTRAINT c_interface_1 FOREIGN KEY(hostid) REFERENCES hosts(hostid)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

# 查看interface表的创建语句
mysql> show create table interface\G;
*************************** 1. row ***************************
       Table: interface
Create Table: CREATE TABLE `interface` (
  `interfaceid` bigint unsigned DEFAULT NULL,
  `ip` varchar(64) DEFAULT '127.0.0.1',
  `hostid` int DEFAULT NULL,
  KEY `c_interface_1` (`hostid`),
  CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified
mysql> 
mysql>

2. 删除外键约束

ALTER TABLE interface DROP FOREIGN KEY c_interface_1;

3. 在修改表时添加外键约束

ALTER TABLE interface ADD CONSTRAINT c_interface_1 FOREIGN KEY(hostid) REFERENCES hosts(hostid);

唯一约束

MySQL唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况。如果主机信息表中的hostid字段，其中一条记录的hostid值为‘76549’，那么该表中就不能出现另一条记录的hostid值也为‘76549’。

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在主机信息表中，为了避免表中主机名重名，可以把主机名设置为唯一约束。

唯一约束的关键字：UNIQUE

1. 在创建表时设置唯一约束

语法

<字段名> <数据类型> UNIQUE

案例：创建users表，userid字段设置主键约束，name字段设置唯一约束

create table users(
userid int(4) PRIMARY KEY,
name varchar(20) unique,
alias varchar(20)
);

2. 在修改表时添加唯一约束

语法：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

案例：将现有的users表中的alias字段设置唯一约束

alter table users add constraint c_1 unique(alias);

3. 删除唯一约束

语法

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

案例：删除名为c_1的唯一约束

# 先查看创建叫的语句，就可以知道约束名叫啥
mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `userid` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `alias` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

# 删除名为c_1的唯一约束
alter table users drop index c_1;

检查约束

MySQL检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

1. 在创建表时设置检查约束

语法

CHECK(<检查约束>)

案例，创建users表，age字段设置检查约束，约束条件是：值大于0和小于100

# 创建users表，age字段设置检查约束
create table users(
userid int(4) primary key,
name varchar(20) unique,
age int,
check(age>0 and age<100)
);

# 查看创建表语句
mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `userid` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `name` (`name`),
  CONSTRAINT `users_chk_1` CHECK (((`age` > 0) and (`age` < 100)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

2. 在修改表时添加检查约束

语法

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

3. 删除检查约束

语法

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

非空约束

MySQL 非空约束（NOT NULL）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。

1. 在创建表时设置非空约束

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：

<字段名> <数据类型> NOT NULL;

2. 在修改表时添加非空约束

如果在创建表时忘记了为字段设置非空约束，也可以通过修改表进行非空约束的添加。

修改表时设置非空约束的语法格式如下：

ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;

3. 删除非空约束

修改表时删除非空约束的语法规则如下：

ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

默认值约束

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

例如，主机信息表中，主机位于A机房的比较多，那么机房位置就可以默认为“A”，系统就会自动为这个字段赋值为“A”。

1. 在创建表时设置默认值约束

语法

<字段名> <数据类型> DEFAULT <默认值>;

2. 在修改表时添加默认值约束

修改表时添加默认值约束的语法格式如下：

ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

3. 删除默认值约束

当一个表中的列不需要设置默认值时，就需要从表中将其删除。

修改表时删除默认值约束的语法格式如下：

ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

“
好了，关于mysql的约束，今晚就此搁笔，时间有限。笔者认为，除了要学好数据库的约束之外，还要学会自我约束，比如约束自己准时睡觉，这不，我要准备睡了，不能熬夜哦！
”

vlambda博客 学习文章列表