MySQL - 05数据完整性

最新推荐文章于 2022-05-12 11:35:36 发布

小沈曰

最新推荐文章于 2022-05-12 11:35:36 发布

阅读量535

点赞数

分类专栏： DBA-MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/shentian885/article/details/106426584

版权

DBA-MySQL 专栏收录该内容

64 篇文章 1 订阅

订阅专栏

什么是数据完整性

数据完整性是指存储在数据库中的数据，应该保持一致性和可靠性。
关系模型允许定义三类数据约束，它们是实体完整性、参照完整性以及用户定义的完整性约束，其中前两种完整性约束由关系数据库系统自动支持。

实体完整性：实体就是现实世界中的某个对象，RDBMS中一行代表一个实体。实体完整性就是保证每一个实体都能被区别。

域完整性：域完整性主要是对列的输入有要求，通过限制列的数据类型、格式或值的范围来实现

参照完整性：主要是表与表之间的关系，可以通过外键来实现

用户自定义完整性：借助存储过程和触发器实现

实体完整性

实体完整性要求每张表都有唯一标识符，每张表中的主键字段不能为空且不能重复。

学号	姓名	地址
2019001	张三	贵州贵阳
2019002	李芳	陕西兴平
2019003	张晓燕	江西南昌
2019004	刘小刚	贵州兴义
2019005	赵天久	湖南常德

插入错误数据
2019003 张晓燕江西南昌

约束方法：唯一性约束、主键约束、标识列

域完整性

域完整性是针对某一具体关系数据库的约束条件，它保证表中某些列不能输入无效的值。
域完整性指列的值域的完整性，如数据类型、格式、值域范围、是否允许空值等。

学号	姓名	地址
2019001	张三	贵州贵阳
2019002	李芳	陕西兴平
2019003	张晓燕	江西南昌
2019004	刘小刚	贵州兴义
2019005	赵天久	湖南常德

插入错误数据
20190601003 李兴才山西大同

约束方法：限制数据类型、检查约束、默认值、非空约束

参照完整性

参照完整性要求关系中不允许引用不存在的实体。

主表：

学号	姓名	地址
2019001	张三	贵州贵阳
2019002	李芳	陕西兴平
2019003	张晓燕	江西南昌
2019004	刘小刚	贵州兴义
2019005	赵天久	湖南常德

从表：

科目	学号	分数
计算机	2019001	99
离散数学	2019001	87
计算机	2019002	95

插入错误数据
计算机 2019008 78

约束方法：外键约束

用户自定义完整性

用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

会员号	姓名	余额
VIP001	张三	67
VIP002	李芳	409
VIP003	张晓燕	112
VIP004	刘小刚	100
VIP005	赵天久	300

插入错误数据
VIP006 李四 -50
约束方法：规则、存储过程、触发器

唯一性约束

在MySQL中，可以使用关键字 UNIQUE 实现字段的唯一性约束，从而保证实体的完整性。

UNIQUE 意味着任何两条数据的同一个字段不能有相同值。
一个表中可以有多个 UNIQUE 约束。

#在创建表时添加唯一性约束

create table person(
id int not null auto_increment primary key comment '主键id', 
name varchar(30) comment '姓名',
id_number varchar(18) unique comment '身份证号'
);

外键约束

外键(FOREIGN KEY)约束定义了表之间的一致性关系，用于强制参照完整性。
外键约束定义了对同一个表或其他表的列的引用，这些列具有PRIMARY KEY或UNIQUE约束。

主表：

#学生表
create table stu(
stu_no int not null primary key comment '学号',
stu_name varchar(30) comment '姓名' );

从表：

#成绩表

create table sc(
id int not null auto_increment primary key comment '主键id', 
stu_no int not null comment '学号',
course varchar(30) comment '课程',
grade int comment '成绩',
foreign key(stu_no) references stu(stu_no)
);

在插入数据时，必须先向主表插入，再向从表插入。删除数据时正好相反。

实战案例

唯一性约束

mysql> create table person(
    -> id int not null auto_increment primary key, 
    -> name varchar(30),
    -> id_number varchar(18) unique
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> desc person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(30) | YES  |     | NULL    |                |
| id_number | varchar(18) | YES  | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into person(name, id_number) values('Lily', '1234567890123478');
Query OK, 1 row affected (0.02 sec)

mysql> insert into person(name, id_number) values('Gray', '1234567890123478');
ERROR 1062 (23000): Duplicate entry '1234567890123478' for key 'person.id_number'
mysql> insert into person(name, id_number) values('Gray', '1234567890123479');
Query OK, 1 row affected (0.01 sec)

mysql> select * from person;
+----+------+------------------+
| id | name | id_number        |
+----+------+------------------+
|  1 | Lily | 1234567890123478 |
|  3 | Gray | 1234567890123479 |
+----+------+------------------+
2 rows in set (0.00 sec)

mysql>

外键约束

mysql> create table stu(
    -> stu_no int not null primary key,
    -> stu_name varchar(30)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create table sc(
    -> id int not null auto_increment primary key, 
    -> stu_no int not null,
    -> course varchar(30),
    -> grade int,
    -> foreign key(stu_no) references stu(stu_no)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into stu(stu_no, stu_name) values('20190001', 'lily');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu(stu_no, stu_name) values('20190002', 'Gary');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+----------+----------+
| stu_no   | stu_name |
+----------+----------+
| 20190001 | lily     |
| 20190002 | Gary     |
+----------+----------+
2 rows in set (0.01 sec)

mysql> insert into sc(stu_no, course, grade) values('20190001', 'math', 98);
Query OK, 1 row affected (0.02 sec)

mysql> insert into sc(stu_no, course, grade) values('20190003', 'math', 87);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`sc`, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`stu_no`) REFERENCES `stu` (`stu_no`))
mysql> insert into stu(stu_no, stu_name) values('20190003', 'Tom');
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc(stu_no, course, grade) values('20190003', 'math', 87);
Query OK, 1 row affected (0.02 sec)

mysql> select * from sc;
+----+----------+--------+-------+
| id | stu_no   | course | grade |
+----+----------+--------+-------+
|  1 | 20190001 | math   |    98 |
|  3 | 20190003 | math   |    87 |
+----+----------+--------+-------+
2 rows in set (0.01 sec)

mysql>