数据库约束-主键约束-唯一约束-非空约束-默认值

Devops海洋的渔夫

发布于 2022-01-17 14:27:45

6K0

发布于 2022-01-17 14:27:45

文章被收录于专栏：Devops专栏Devops专栏

10. 数据库约束-主键约束-唯一约束-非空约束-默认值

约束概述

约束其实就是一种限制,用于修饰表中的列. 通过这种限制来保证表中数据的正确性、有效性和完整性。

约束的类型

约束名	约束关键字
主键	primary key
非空	not null
唯一	unique
外键	foreign key ... references
默认	default

1. 主键约束(重点)

1.1 主键的作用

用来标注一条记录的唯一性，每个表都应该有一个主键，并且每个表只能有一个主键。有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录冗余不唯一,这样就不方便管理数据

哪个字段应该作为表的主键？通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

1.2 创建主键

# 主键：PRIMARY KEY
# 主键的特点：
 1. 主键字段值唯一(唯一不可重复)
 2. 主键列不能为NULL
 3. 一个表中只能有一个主键,但主键可以是多个字段 (联合主键)

创建主键方式：

-- 方式1:创建表时,添加主键约束 (字段约束区)
 格式:
  create table 表名 ( 
   字段名称1 字段类型 字段约束区,  
   字段名称2 字段类型 字段约束区
  );

-- 方式2:创建表时,在约束区域添加约束(扩展)
 格式1:单一主键
  create table 表名 (
      字段名称1 字段类型,  
      字段名称2 字段类型,  
      primary key(字段名称1)  
  );

 格式2:联合主键
  create table 表名 (
      字段名称1 字段类型,  
      字段名称2 字段类型,  
      primary key(字段名称,字段名称1)  
  );

-- 方式3:给已有表中添加主键(扩展)
 alter table 表名 add primary key(字段名称);

具体操作:

1.2.1 创建表用户表, 包含字段(id, name)将id做为主键

方式1: 建表时在字段的约束区添加主键约束

-- 主键约束
-- 方式1: 建表时在字段的约束区添加主键约束
CREATE TABLE user1(
 id INT PRIMARY KEY, # 在字段的约束区添加主键约束
 `name` VARCHAR(20)
);
-- 添加数据信息
INSERT INTO user1 VALUES(1,'tom'); -- 正常插入
INSERT INTO user1 VALUES(NULL,'tom'); -- 插入异常(主键不可为空): Column 'id' cannot be null
INSERT INTO user1 VALUES(1,'tom'); -- 插入异常(主键不可重复): Duplicate entry '1' for key 'PRIMARY'

执行如下：

-- 方式1: 建表时在字段的约束区添加主键约束
mysql> CREATE TABLE user1(
    -> id INT PRIMARY KEY, # 在字段的约束区添加主键约束
    -> `name` VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 查看创建后的所有表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| product       |
| stu3          |
| user1         |
+---------------+
3 rows in set (0.01 sec)

mysql> 
-- 正常插入
mysql> INSERT INTO user1 VALUES(1,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

mysql> 

-- 插入异常(主键不可为空): Column 'id' cannot be null
mysql> INSERT INTO user1 VALUES(NULL,'tom');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> 

-- 插入异常(主键不可重复): Duplicate entry '1' for key 'PRIMARY'
mysql> INSERT INTO user1 VALUES(1,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

方式2: 建表时在约束区添加主键约束

-- 方式2: 建表时在约束区添加主键约束
CREATE TABLE user2(
 id INT, 
 `name` VARCHAR(20),
 PRIMARY KEY (id)
);

执行如下：

-- 方式2: 建表时在约束区添加主键约束
mysql> CREATE TABLE user2(
    -> id INT, 
    -> `name` VARCHAR(20),
    -> PRIMARY KEY (id) -- 设置主键约束
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 查看user2与user1的表结构
mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | -- 可以看到 id 的 key 值为 PRI 与 user1 表一致
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | 
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

方式3: 创建完表后,通过修改表结构添加主键约束

-- 方式3: 创建完表后,通过修改表结构添加主键约束
CREATE TABLE user3(
 id INT, 
 `name` VARCHAR(20)
);
-- 修改表添加主键
ALTER TABLE user3 ADD PRIMARY KEY (id);

执行如下：

-- 方式3: 创建完表后,通过修改表结构添加主键约束
mysql> CREATE TABLE user3(
    -> id INT, 
    -> `name` VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)
-- 修改表添加主键
mysql> ALTER TABLE user3 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看user3的表结构，可以看到 id 已经设置为主键
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

1.2.2 创建表用户表, 包含字段(id, name) 将id和name做为联合主键

上面我们将 id 作为单一主键，而还有多个字段作为联合主键的情况，如下：

image-20200527101914892

image-20200527101757783

-- 联合主键
CREATE TABLE user4(
 id INT, 
 `name` VARCHAR(20),
 PRIMARY KEY (id,`name`)
);
-- 保证id和name字段不为空,然后联合判断唯一性
INSERT INTO user4 VALUES(1,'tom'); -- 正常插入
INSERT INTO user4 VALUES(2,"tom"); -- 正常插入
INSERT INTO user4 VALUES(1,"rose"); -- 正常插入
INSERT INTO user4 VALUES(2,"rose"); -- 正常插入
INSERT INTO user4 VALUES(1,"tom"); -- 插入异常:Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO user4 VALUES(NULL,"jack"); -- 插入异常:Column 'id' cannot be null
INSERT INTO user4 VALUES(3,NULL); -- 插入异常:Column 'name' cannot be null

“注意 : 一张表中只有一个主键 , 主键可以为多个字段 , 不过我们一般增加一个字段 id 来作为主键. ”

执行如下：

-- 联合主键: 包含 id 与 name 两个字段作为主键，要求表中的数据 id 与 name 结合在一起不能重复
mysql> CREATE TABLE user4(
    -> id INT, 
    -> `name` VARCHAR(20),
    -> PRIMARY KEY (id,`name`)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 保证id和name字段不为空,然后联合判断唯一性
-- 正常插入
mysql> INSERT INTO user4 VALUES(1,'tom');
Query OK, 1 row affected (0.01 sec)

-- 正常插入: 虽然 name 字段与上一条数据重复，但是 id 不一致
mysql> INSERT INTO user4 VALUES(2,"tom");
Query OK, 1 row affected (0.01 sec)

-- 正常插入: id 与 第一条数据一致，但是 name 数据不一致
mysql> INSERT INTO user4 VALUES(1,"rose");
Query OK, 1 row affected (0.00 sec)

-- 正常插入
mysql> INSERT INTO user4 VALUES(2,"rose");
Query OK, 1 row affected (0.00 sec)

-- 插入异常:Duplicate entry '1-tom' for key 'PRIMARY'
mysql> INSERT INTO user4 VALUES(1,"tom");
ERROR 1062 (23000): Duplicate entry '1-tom' for key 'PRIMARY'

-- 插入异常:Column 'id' cannot be null
mysql> INSERT INTO user4 VALUES(NULL,"jack");
ERROR 1048 (23000): Column 'id' cannot be null

-- 插入异常:Column 'name' cannot be null
mysql> INSERT INTO user4 VALUES(3,NULL);
ERROR 1048 (23000): Column 'name' cannot be null

1.3 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

具体操作:

删除user1表的主键

ALTER TABLE user1 DROP PRIMARY KEY;

执行如下：

-- 删除主键之前的表结构
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | -- id 为主键
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- 删除user1表的主键
mysql> alter table user1 drop primary key;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- 删除主键之后的表结构，可以看到 id 已经不是主键了。
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

1.4 主键自增 AUTO_INCREMENT

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值AUTO_INCREMENT 表示自动增长(字段类型是整型数字)

具体操作:

创建学生表user5, 包含字段(id, name)将id做为主键并自动增长

CREATE TABLE user5 (
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(20)
);

插入数据

-- 主键默认从1开始自动增长
INSERT INTO user5 (name) VALUES ('唐僧');
INSERT INTO user5 (name) VALUES ('孙悟空');
INSERT INTO user5 VALUES (null,'猪八戒');
INSERT INTO user5 VALUES (null,'沙僧');

执行如下：

-- 创建user5表，并设置 id 字段为自增主键
mysql> CREATE TABLE user5 (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入数据
mysql> INSERT INTO user5 (name) VALUES ('唐僧');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO user5 (name) VALUES ('孙悟空');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user5 VALUES (null,'猪八戒');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user5 VALUES (null,'沙僧');
Query OK, 1 row affected (0.00 sec)

-- 查看 user5 表中的 id 数据 从 1 开始自增
mysql> select * from user5;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 唐僧      |
|  2 | 孙悟空    |
|  3 | 猪八戒    |
|  4 | 沙僧      |
+----+-----------+
4 rows in set (0.00 sec)

mysql>

DELETE和TRUNCATE的区别

DELETE删除表中的数据，不重置AUTO_INCREMENT的值
TRUNCATE摧毁表，重建表，AUTO_INCREMENT重置为1
如果想自定义id的值,可以使用下面的sql进行设置,一般不需要
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

下面我们来演示一下：

演示 DELETE 删除表数据对于自增字段的影响

-- delete 删除之前的 user5 表数据，此时自增 id = 4
mysql> select * from user5;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 唐僧      |
|  2 | 孙悟空    |
|  3 | 猪八戒    |
|  4 | 沙僧      |
+----+-----------+
4 rows in set (0.00 sec)

-- delete 删除表数据
mysql> delete from user5;
Query OK, 4 rows affected (0.00 sec)

-- 插入一条数据，然后查看表中的 id 自增到 5. 
mysql> INSERT INTO user5 VALUES (null,'沙僧');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  5 | 沙僧   | -- 说明 delete 删除的表数据，并不会重置 id 的自增，而是接着之前的自增值继续自增
+----+--------+
1 row in set (0.00 sec)

mysql>

演示 TRUNCATE 删除表数据对于自增字段的影响

-- truncate 表之前的 user5 数据
mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  5 | 沙僧   |
+----+--------+
1 row in set (0.00 sec)

-- truncate 删除 user5 表数据
mysql> truncate table user5;
Query OK, 0 rows affected (0.01 sec)

-- 再插入一条数据，可以发现自增id重置为1
mysql> INSERT INTO user5 VALUES (null,'沙僧');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  1 | 沙僧   |
+----+--------+
1 row in set (0.00 sec)

mysql>

修改表的主键自增初始值

-- 修改 user5 表的自增初始值为 10
mysql> alter table user5 auto_increment=10;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 再插入一条数据，看看自增值
mysql> INSERT INTO user5 VALUES (null,'沙僧');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  1 | 沙僧   |
| 10 | 沙僧   | -- 可以看到从 10 开始自增了。
+----+--------+
2 rows in set (0.00 sec)

mysql>

2. 唯一约束 UNIQUE

在这张表中这个字段的值不能重复

2.1 唯一约束的基本格式

字段名字段类型 UNIQUE

2.2 实现唯一约束

具体操作:

创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

CREATE TABLE st7 (
 id INT,
 NAME VARCHAR(20) UNIQUE
);

添加数据，查看数据重复的问题

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st7 VALUES (5, NULL);
INSERT INTO st7 VALUES (6, NULL);

执行如下：

-- 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
mysql> CREATE TABLE st7 (
    -> id INT,
    -> NAME VARCHAR(20) UNIQUE
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 插入四条数据
mysql> INSERT INTO st7 VALUES (1, '貂蝉');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO st7 VALUES (2, '西施');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (3, '王昭君');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (4, '杨玉环');
Query OK, 1 row affected (0.00 sec)

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
mysql> INSERT INTO st7 VALUES (5, '貂蝉');
ERROR 1062 (23000): Duplicate entry '貂蝉' for key 'NAME'

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
mysql> INSERT INTO st7 VALUES (5, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (6, NULL);
Query OK, 1 row affected (0.00 sec)

-- 可以看到 null 可以重复插入
mysql> select * from st7;
+------+-----------+
| id   | NAME      |
+------+-----------+
|    1 | 貂蝉      |
|    2 | 西施      |
|    3 | 王昭君    |
|    4 | 杨玉环    |
|    5 | NULL      |
|    6 | NULL      |
+------+-----------+
6 rows in set (0.00 sec)

mysql>

3. 非空约束 NOT NULL

被修饰的这个字段必须设置值,不能是NULL

3.1 非空约束的基本格式

字段名字段类型 NOT NULL

3.2 实现非空约束

具体操作:

创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
 id INT,
 NAME VARCHAR(20) NOT NULL,
 gender CHAR(2)
);

添加一条完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

执行如下：

-- 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL
mysql> CREATE TABLE st8 (
    -> id INT,
    -> NAME VARCHAR(20) NOT NULL,
    -> gender CHAR(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入四条数据
mysql> INSERT INTO st8 VALUES (1, '郭富城', '男');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO st8 VALUES (2, '黎明', '男');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st8 VALUES (3, '张学友', '男');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st8 VALUES (4, '刘德华', '男');
Query OK, 1 row affected (0.00 sec)

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
mysql> INSERT INTO st8 VALUES (5, NULL, '男');
ERROR 1048 (23000): Column 'NAME' cannot be null
mysql> 

-- 可以看到设置了非空约束后，st8表无法插入 null 数据
mysql> select * from st8;
+------+-----------+--------+
| id   | NAME      | gender |
+------+-----------+--------+
|    1 | 郭富城    | 男     |
|    2 | 黎明      | 男     |
|    3 | 张学友    | 男     |
|    4 | 刘德华    | 男     |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql>

4. 默认值 DEFAULT

往表中添加数据时,如果不指定这个字段的数据,就使用默认值

4.1 默认值格式

字段名字段类型 DEFAULT 默认值

4.2 实现字段默认值

具体操作:

创建一个学生表 st9，包含字段(id,name,address)，地址默认值是广州

CREATE TABLE st9 (
 id INT,
 NAME VARCHAR(20),
 address VARCHAR(50) DEFAULT '广州'
);

添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

添加一条记录,不使用默认地址

INSERT INTO st9 VALUES (2, '张学友', '中国香港');

执行如下：

-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
mysql> CREATE TABLE st9 (
    -> id INT,
    -> NAME VARCHAR(20),
    -> address VARCHAR(50) DEFAULT '广州' -- 设置默认值
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 插入一条数据，不填写 address 的值
mysql> INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');
Query OK, 1 row affected (0.00 sec)

-- 可以看到数据自动设置为默认值: 广州
mysql> select * from st9;
+------+-----------+---------+
| id   | NAME      | address |
+------+-----------+---------+
|    1 | 刘德华    | 广州    |
+------+-----------+---------+
1 row in set (0.00 sec)

-- 添加一条记录,不使用默认地址
mysql> INSERT INTO st9 VALUES (2, '张学友', '中国香港');
Query OK, 1 row affected (0.00 sec)

-- 如果设置了值，那么则不会写入默认值
mysql> select * from st9;
+------+-----------+---------+
| id   | NAME      | address |
+------+-----------+---------+
|    1 | 刘德华    | 广州    |
|    2 | 张学友    | 中国香港    | -- 使用输入的值
+------+-----------+---------+
2 rows in set (0.00 sec)

mysql>

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-02-22，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

数据分析

本文分享自海洋的渔夫微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

数据分析

登录后参与评论

0 条评论

热度