本文详细介绍了数据库约束中的主键约束、唯一约束、非空约束和默认值的概念及应用。主键用于标识记录的唯一性，通常设置为自增字段；唯一约束确保字段值不重复，但允许NULL；非空约束则要求字段值不能为空；默认值在未指定字段值时自动填充。通过示例展示了各种约束在创建表和插入数据时的使用和影响。
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 作为单一主键，而还有多个字段作为 联合主键的情况，如下：
-- 联合主键
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>
扫一扫
9008
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
