MySQL-数据库约束

KYumii

于 2024-09-28 22:17:41 发布

阅读量639

点赞数 9

文章标签：数据库 mysql oracle

本文链接：https://blog.csdn.net/2301_80418043/article/details/142622033

版权

1.约束类型

类型	说明
NOT NULL非空约束	指定非空约束的列不能存储NULL值
DEFAULT默认约束	当没有给列赋值时使用的默认值
UNIQUE唯一约束	指定唯一约束的列每行数据必须有唯一的值
PRIMARY KEY主键约束	NOT NULL和UNIQUE的结合，可以指定一个列霍多个列，有助于防止数据重复和提高数据的查询性能
FOREIGN KEY外键约束	外键约束是一种关系约束，用于定义两个表之间的关联关系，可以确保数据的完整性和一致性
CHECK约束	用于限制列或数据在数据库表中的值，确保数据的准确性和可靠性

2.NOT NULL非空约束

定义表时某列不允许为NULL时，可以为列添加非空约束

⽐如创建⼀个学⽣表，学⽣名为NULL时，这条记录是不完整的

drop table if exists student;
create table student(
 id bigint,
 name varchar(20)
);

# 插⼊数据
insert into student values (1, null);

# 查询
select * from student;
+------+------+
| id | name |
+------+------+
| 1 | NULL | # 班级名为NULL,没有意义
+------+------+
1 row in set (0.00 sec)

此时需要约束学生名的列不能为NULL

drop table if exists student;
# 为所有列添加⾮空约束
create table student (
 id bigint,
 name varchar(20) NOT NULL
);

# 由于name列有⾮空约束，插⼊NULL值时报错
insert into student values (1, null);
ERROR 1048 (23000): Column 'name' cannot be null

# 正常值可以成功插⼊
insert into student values (1, '张三');
Query OK, 1 row affected (0.00 sec)
select * from student;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.00 sec)

查看表结构，NULL列为NO表⽰值不允许为NULL，YES表⽰值可以为NULL

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.DEFALUT默认值约束

DEFAULT 约束⽤于向列中插⼊默认值，如果没有为列设置值，那么会将默认值设置到该列

重构学⽣表，新增年龄列

drop table student;
# 创建学⽣表，加⼊年龄列
create table student (
 id bigint,
 name varchar(20) not null,
 age int
);

插⼊⼀条记录，没有设置默认约束时，不指定年龄的值时列为NULL

insert into student(id, name) values (1, '张三');

select * from student;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | NULL | # 年龄值为NULL
+------+--------+------+
1 row in set (0.00 sec)

重构学⽣表，为年龄的列加⼊默认约束

drop table student;

# 为年龄列加⼊默认约束

create table student (
 id bigint,
 name varchar(20) not null,
 age int DEFAULT 18
);

插⼊⼀条记录，不指定年龄的值时列使⽤了默认值

select * from student;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | 18 |
+------+--------+------+
1 row in set (0.00 sec)

查看表结构，年龄列的默认值为18

desc student;
+-------+-------------+------+-----+---------+-------+
| Field |         Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|    id |      bigint |   YES |     |    NULL |       |
|  name | varchar(20) |    NO |     |    NULL |       |
|   age |         int |   YES |     |      18 |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

当手动明确值年龄列为NULL时列值为NULL

insert into student(id, name, age) values (2, '李四', NULL);
Query OK, 1 row affected (0.00 sec)
select * from student;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 |  张三 |   18 |
| 2 |  李四 | NULL |
+------+--------+------+
2 rows in set (0.00 sec)

4.UNIQUE唯一约束

指定了唯⼀约束的列，该列的值在所有记录中不能重复，⽐如⼀个⼈的⾝份证号，学⽣的学号等

重构学生表，新增学号列

drop table student;
# 学号列设置唯⼀约束
create table student (
 id bigint,
 name varchar(20) not null,
 age int DEFAULT 18,
 sno varchar(10)
);

不设置唯⼀约束时，学号可以重复

insert into student(id, name, sno) values (1, '张三', '100001');
Query OK, 1 row affected (0.00 sec)

insert into student(id, name, sno) values (2, '李四', '100001');
Query OK, 1 row affected (0.00 sec)

select * from student;
+------+--------+------+--------+
| id | name | age | sno |
+------+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 李四 | 18 | 100001 |
+------+--------+------+--------+
2 rows in set (0.00 sec)

重构学⽣表，为学号列设置唯⼀约束

drop table student;
create table student (
 id bigint,
 name varchar(20) not null,
 age int DEFAULT 18,
 sno varchar(10) UNIQUE # 唯⼀约束
);

插⼊重复的学号时报错，唯⼀约束⽣效

insert into student(id, name, sno) values (1, '张三', '100001');
Query OK, 1 row affected (0.01 sec)

insert into student(id, name, sno) values (2, '李四', '100001');
ERROR 1062 (23000): Duplicate entry '100001' for key 'student.sno'

select * from student;
+------+--------+------+--------+
| id | name | age | sno |
+------+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
+------+--------+------+--------+
1 row in set (0.00 sec)

查看表结构，Key列显⽰UNI表⽰唯⼀约束

desc student;
+-------+-------------+------+-----+---------+-------+
| Field |        Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|    id |      bigint |  YES |     |    NULL |       |
|  name | varchar(20) |   NO |     |    NULL |       |
|   age |         int |  YES |     |      18 |       |
|   sno | varchar(10) |  YES | UNI |    NULL |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5.PRIMARY KEY主键约束

主键约束唯⼀标识数据库表中的每条记录。

主键必须包含唯⼀的值，且不能包含 NULL 值。

每个表只能有⼀个主键，可以由单个列或多个列组成。

通常为每张表都指定⼀个主键，主键列建议使⽤BIGINT类型

重构学⽣表，为ID列添加⾮空和唯⼀约束

drop table student;
create table student (
 id bigint not null unique,
 name varchar(20) not null,
 age int DEFAULT 18,
 sno varchar(10) UNIQUE
);

查看表结构，添加了⾮空和唯⼀约束之后Key列显⽰PRI表⽰主键

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type　　　　　 | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 　　id | 　　　bigint |　　 NO | PRI | 　NULL |　　　　 | # 主键
| 　name | varchar(20) | 　　NO | 　　　|　 NULL |　　　　 |
| 　　age| 　　　　　int | 　YES | 　　　|　 18 |　　　　　 |
| 　　sno| varchar(10) |　　 YES | UNI | 　NULL | 　　　　|
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

当Id列的重复时会发⽣主键冲突

insert into student(id, name, sno) values (1, '张三', '100001');
Query OK, 1 row affected (0.01 sec)

insert into student(id, name, sno) values (1, '李四', '100001');
ERROR 1062 (23000): Duplicate entry '1' for key 'student.id'

通常把主键列设置为⾃动增⻓，让数据库维护主键值

drop table student;

# 重构学⽣表
create table student (
 id bigint PRIMARY KEY auto_increment, # 设置⾃增主键
 name varchar(20) not null,
 age int DEFAULT 18,
 sno varchar(10) UNIQUE
);

插⼊数据时不设置主键列的值

# 主键列的值为NULL
insert into student(id, name, sno) values (NULL, '张三', '100001');
Query OK, 1 row affected (0.00 sec)

# 不指定主键
insert into student(name, sno) values ('李四', '100002');
Query OK, 1 row affected (0.01 sec)

# 主键列的值⾃动⽣成
select * from student;
+----+--------+------+--------+
| id | name | age | sno |
+----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 李四 | 18 | 100002 |
+----+--------+------+--------+
2 rows in set (0.00 sec)

查看表结构，Extra列显⽰auto_increment 表⽰⾃增

desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | 　　　　Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| 　　id | 　　　bigint | NO | PRI |　　 NULL | auto_increment |
| 　name | varchar(20) | NO |　　 | 　　NULL |　　　　　　　　　 |
| 　　age | 　　　　int | YES | 　　|　　　 18 |　　　　　　　　 |
|　　 sno | varchar(10) | YES | UNI |　 NULL |　　　　　　　　 |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

如果某条记录写⼊失败，新⽣成的主键值将会作废

# 由于学号重复，产⽣了唯⼀冲突，导致插⼊失败，ID为3的主键值作废
insert into student(name, sno) values ('王五', '100002');
ERROR 1062 (23000): Duplicate entry '100002' for key 'student.sno'

# 修改学号后成功插⼊数据
insert into student(name, sno) values ('王五', '100003');
Query OK, 1 row affected (0.00 sec)

# 查询后发现新记录ID列的值为4
select * from student;
+----+--------+------+--------+
| id | name | age | sno |
+----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 | # 新插⼊的记录
+----+--------+------+--------+
3 rows in set (0.00 sec)

主键值可以不连续

# ⼿动指定⼀个值
insert into student(id, name, sno) values (100, '赵六', '100004');
Query OK, 1 row affected (0.01 sec)

select * from student;
+-----+--------+------+--------+
| id | name | age | sno |
+-----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 |
| 100 | 赵六 | 18 | 100004 |
+-----+--------+------+--------+
4 rows in set (0.00 sec)

# 下⼀次⾃增从主键的最⼤值开始
insert into student(name, sno) values ('钱七', '100005');
Query OK, 1 row affected (0.01 sec)

select * from student;
+-----+--------+------+--------+
| id | name | age | sno |
+-----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 |
| 100 | 赵六 | 18 | 100004 |
| 101 | 钱七 | 18 | 100005 | # ID列的值是101
+-----+--------+------+--------+
5 rows in set (0.00 sec)

主键或唯⼀键冲突时的更新操作，插⼊否则更新

# 语法
INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

# 插⼊ID为100，学号为100100的学⽣记录时，报主键冲突
insert into student(id, name, sno) values (100, '赵六', '100100');
ERROR 1062 (23000): Duplicate entry '100' for key 'student.PRIMARY'

# 可以使⽤以上语法，如果插⼊时有冲突则更新当前列的值
insert into student(id, name, sno) values (100, '赵六', '100100')
 -> ON DUPLICATE KEY UPDATE name = '赵六', sno = '100100';
Query OK, 2 rows affected (0.01 sec) 

# 两⾏受影响，表⽰删除了原来的记录，⼜新写⼊了⼀条记录
# 与update student set name = '赵六', sno = '100100' where id = 100; 等效

select * from student;
+-----+--------+------+--------+
| id | name | age | sno |
+-----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 |
| 100 | 赵六 | 18 | 100100 | # 学号已修改
| 101 | 钱七 | 18 | 100005 |
+-----+--------+------+--------+
5 rows in set (0.00 sec)

替换，如果存在冲突则替换，不存在冲突则插⼊

# 语法
REPLACE [INTO] table_name
 [(column [, column] ...)]
VALUES
 (value_list) [, (value_list)] ...
value_list: value, [, value] ...

# 写⼊或更新Id为101的记录
REPLACE into student(id, name, sno) values (101, '钱七', '100101');
Query OK, 2 rows affected (0.01 sec) # 受影响两⾏

# 原数据已更新
select * from student;
+-----+--------+------+--------+
| id | name | age | sno |
+-----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 |
| 100 | 赵六 | 18 | 100100 |
| 101 | 钱七 | 18 | 100101 |
+-----+--------+------+--------+
5 rows in set (0.00 sec)

# 写⼊⼀条新数据
REPLACE into student(id, name, sno) values (102, '吴⼋', '100102');
Query OK, 1 row affected (0.01 sec) # 受影响⼀⾏

# 成功写⼊
select * from student;
+-----+--------+------+--------+
| id | name | age | sno |
+-----+--------+------+--------+
| 1 | 张三 | 18 | 100001 |
| 2 | 王五 | 18 | 100002 |
| 4 | 王五 | 18 | 100003 |
| 100 | 赵六 | 18 | 100100 |
| 101 | 钱七 | 18 | 100101 |
| 102 | 吴⼋ | 18 | 100102 |
+-----+--------+------+--------+
6 rows in set (0.00 sec)

表中不能有多个主键

drop table student;
# 重构学⽣表
create table student (
 id bigint PRIMARY KEY auto_increment, # 定义主键
 name varchar(20) PRIMARY KEY # 定义主键
);
ERROR 1068 (42000): Multiple primary key defined # 报错

复合主键：由多个列共同组成的主键，主键是否冲突以多个列的组成进⾏判定

drop table student;
# 重构学⽣表
create table student (
 id bigint,
 name varchar(20),
 PRIMARY KEY (id, name) # 指定复合主键
);

# 插⼊数据
insert into student(id, name) values (1, '张三');
Query OK, 1 row affected (0.01 sec)

# 重复插⼊主键冲突，此时主键值由id和name两个列共同决定
insert into student(id, name) values (1, '张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'student.PRIMARY'

# 修改id值插⼊成功
insert into student(id, name) values (2, '张三');
Query OK, 1 row affected (0.00 sec)

select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
+----+--------+
2 rows in set (0.00 sec)

6.FOREIGN KEY外键约束

外键⽤于定义主表和从表之间的关系

外键约束主定义在从表的列上，主表关联的列必须是主键或唯⼀约束

当定义外键后，要求从表中的外键列数据必须在主表的主键或唯⼀列存在或为null。

创建班级表(主表)，并初始化数据

drop table if exists class;
# 建表
create table class (
 id bigint primary key auto_increment,
 name varchar(20) not null
);

# 初始化数据
insert into class (name) values ('java01'), ('java02'), ('java03'), ('C++01'), 
('C++02');
Records: 5 Duplicates: 0 Warnings: 0

select * from class;
+----+--------+
| id | name |
+----+--------+
| 1 | java01 |
| 2 | java02 |
| 3 | java03 |
| 4 | C++01 |
| 5 | C++02 |
+----+--------+
5 rows in set (0.00 sec)

重构学生表（从表），加入外键约束

# 语法
foreign key (id) references class(id)

drop table if exists student;

# 重构表
create table student(
 id bigint PRIMARY KEY auto_increment, 
 name varchar(20) not null,
 age int DEFAULT 18,
 class_id bigint,
 foreign key (class_id) references class(id) # 创建外键约束
);

查看表结构，Key列的值为MUL表⽰外键约束的列

desc student;
+----------+-------------+------+-----+---------+----------------+
|    Field |        Type | Null | Key | Default |          Extra |
+----------+-------------+------+-----+---------+----------------+
|       id |      bigint |   NO | PRI |    NULL | auto_increment |
|     name | varchar(20) |   NO |     |    NULL |                |
|      age |         int |  YES |     |      18 |                |
| class_id |      bigint |  YES | MUL |    NULL |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

正常插⼊数据

# 班级编号在主表中存在
insert into student(name, class_id) values ('张三', 1), ('李四', 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

# 插⼊成功
select * from student;
+----+--------+------+----------+
| id | name | age | class_id |
+----+--------+------+----------+
|  1 | 张三 |  18 |        1 |
|  2 | 李四 |  18 |        2 |
+----+--------+------+----------+
2 rows in set (0.00 sec)

插⼊⼀个班级号为100的学⽣，由于主表中没有这个班级，插⼊失败

# 班级编号在主表中不存在，提⽰外键约束限制导致插⼊失败
insert into student(name, class_id) values ('王五', 100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`java01`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY 
(`class_id`) REFERENCES `class` (`id`))

插⼊班级Id为NULL的记录，可以成功，表⽰当前学⽣还没有分配置班级

# 班级为NULL
insert into student(name, class_id) values ('王五', NULL);
Query OK, 1 row affected (0.00 sec)
select * from student;
+----+--------+------+----------+
| id | name | age | class_id |
+----+--------+------+----------+
|  1 | 张三 |  18 |        1 |
|  2 | 李四 |  18 |        2 |
|  4 | 王五 |  18 |     NULL |
+----+--------+------+----------+
3 rows in set (0.00 sec)

删除主表某条记录时，从表中不能有对该记录的引⽤

# 删除从表中没有引⽤的记录，可以成功
delete from class where name = 'java03';
Query OK, 1 row affected (0.00 sec)

select * from class;
+----+--------+
| id | name |
+----+--------+
| 1 | java01 |
| 2 | java02 |
| 4 | C++01 |
| 5 | C++02 |
+----+--------+
4 rows in set (0.00 sec)

# 删除从表中引⽤的记录，失败
delete from class where name = 'java01';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (`java01`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY 
(`class_id`) REFERENCES `class` (`id`))

删除主表时要先删除从表

# 从表存在是不能删除主表
drop table class;
ERROR 3730 (HY000): Cannot drop table 'class' referenced by a foreign key 
constraint 'student_ibfk_1' on table 'student'.

# 删除从表
drop table student;
Query OK, 0 rows affected (0.02 sec)

# 再删除主表，成功
drop table class;
Query OK, 0 rows affected (0.01 sec)

7.CHECK约束

可以应⽤于⼀个或多个列，⽤于限制列中可接受的数据值，从⽽确保数据的完整性和准确性。

在8.0.16开始全⾯⽀持CHECK约束，之前的版本会忽略CHECK的定义

重构学⽣表，有以下要求，年龄不能⼩于16岁，性别只能是男或⼥

drop table if exists student;

# 加⼊CHECK约束
create table student(
 id bigint PRIMARY KEY auto_increment, # 设置⾃增主键
 name varchar(20) not null,
 age int DEFAULT 18,
 gender char(1),
 check (age >= 16),
 check (gender = '男' or gender = '⼥')
);

# 正常插⼊数据
mysql> insert into student(name, age, gender) values ('张三', 17, '男'), ('李
四', 19, '⼥');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 1 | 张三 | 17 | 男 |
| 2 | 李四 | 19 | ⼥ |
+----+--------+------+--------+
2 rows in set (0.00 sec)

# 插⼊年龄⼩于16岁的记录，失败
mysql> insert into student(name, age, gender) values ('张三', 15, '男');
ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.

# 插⼊性别的值不是男或⼥的记录，失败
mysql> insert into student(name, age, gender) values ('张三', 17, '1');
ERROR 3819 (HY000): Check constraint 'student_chk_2' is violated.

创建新表，c1的值不能为0，c2的值必须⼤于0，c3的值不⼩于c2

# 列与列之间也可以⽐较，需要在单独⼀⾏中定义
create table t_check (
 c1 int check(c1 <> 0),
 c2 int check(c2 > 0),
 c3 int,
 check(c3 >= c2)
);

# 插⼊正常数据
mysql> insert into t_check values (-1, 3, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_check;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| -1 | 3 | 10 |
+------+------+------+
1 row in set (0.00 sec)

# c1 = 0时，失败
mysql> insert into t_check values (0, 5, 6);
ERROR 3819 (HY000): Check constraint 't_check_chk_1' is violated.

# c2 <= 0时，失败
mysql> insert into t_check values (2, -10, 10);
ERROR 3819 (HY000): Check constraint 't_check_chk_2' is violated.

# c3 < c2时，失败
mysql> insert into t_check values (2, 10, 9);
ERROR 3819 (HY000): Check constraint 't_check_chk_3' is violated.