关系模型的完整性约束

关系模型的完整性约束

在数据库中，通常有多个关系（行），为了保证数据的完整性，通常都会有一些限制或约束，这就是关系模型的完整性约束。

完整性约束主要分为三类：实体完整性约束、参照完整性约束和用户定义完整性约束。

实体完整性约束

实体完整性约束对应的就是MySQL中的主键约束，即定义为主键的属性（列） 每一行的内容不能重复且不能为空。

性质

• 实体完整性约束是对关系的约束
• 每个关系必须有主码，主码的值唯一，用于表示关系的元组
• 组成主码的属性都不能取空值，而不仅仅是主码属性集整体不能取空值

参照完整性约束

参照完整性约束就是MySQL中的外键约束。

现实世界中的实体之间往往存在某种联系，在关系模型中实体及实体间的联系都是用关系来描述的，这样就自然存在着关系与关系之间的参照（引用） 关系。
当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性。

例如，以下有两张表，分别为订单表和用户表。

id	number	uid
1	hm001	1
2	hm002	1
3	hm003	2
4	hm004	2

id	name
1	张三
2	李四

在这两张表中，订单和单个用户对应的关系是多对一。因此，订单表中的外键就是uid，即用户表中的id。也就是说订单表引用了用户表中的id。

若现在要新增订单信息，订单的uid不在用户表中，或要在用户表中删除一个用户，而这个用户在订单表中有过订购记录，DBMS就应该对这样的操作加以限制。

# 创建user用户表
create table demo.user (
    id int primary key auto_increment,
    name varchar(20) not null
);

# 添加用户数据
insert into demo.user values (null, '张三'), (null, '李四');

# 创建orderlist订单表
create table orderlist (
    id int primary key auto_increment,
    number varchar(20) not null,
    uid int,
    constraint ou_fk1 foreign key (uid) references user(id)
)

# 添加订单数据
insert into orderlist values (null, 'hm001', 1), (null, 'hm002', 1), (null, 'hm003', 2), (null, 'hm004', 2);

也可以直接添加外键约束

alter table demo.orderlist add constraint ou_fk1 foreign key (uid) references user(id);

接下来实验一下往订单表中添加一个订单，但是在用户表中没有该用户。

insert into orderlist values (null, 'hm005', 3);

运行添加失败

[23000][1452] Cannot add or update a child row: a foreign key constraint fails (`demo`.`orderlist`, CONSTRAINT `ou_fk1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))

接下来尝试删除用户表中的用户李四

delete from user where name = '李四';

因为在订单表中有李四的订购记录，所以同样删除失败。

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`orderlist`, CONSTRAINT `ou_fk1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))

用户定义完整性约束

用户定义完整性约束由用户根据应用需要定义，主要是对属性的取值进行限定，所以主要是域完整性约束。

域完整性约束对属性的取值进行限定，例如学生的成绩必须是0-100，性别必须是男或女。

在MySQL中，使用check约束来实现域完整性约束。

DDL

create table sale_list
(
    sid        int auto_increment
        primary key,
    uid        int          not null,
    fid        varchar(100) not null,
    date       date         not null,
    unit_price double       not null,
    quantity   double       not null,
    is_sold    tinyint(1)   not null,
    sold_date  date         null,
    sold_price double       null,
);

添加约束

alter table fund.sale_list add check ( unit_price < 10 and unit_price > 0)

也可以直接在DDL中添加check约束

create table sale_list
(
    sid        int auto_increment
        primary key,
    uid        int          not null,
    fid        varchar(100) not null,
    date       date         not null,
    unit_price double       not null,
    quantity   double       not null,
    is_sold    tinyint(1)   not null,
    sold_date  date         null,
    sold_price double       null,
    check ((`unit_price` < 10) and (`unit_price` > 0))
);

如果插入了不符合check约束条件的数据，控制台就会报错。
例如：

insert into fund.sale_list values(null, 1, "003096", "2022-12-24", 20, 160, 0, null, null);

[HY000][3819] Check constraint 'sale_list_chk_1' is violated.

在这里基金的净值$20$超过基金净值的最大范围

例题

1. 下列关于实体完整性约束的叙述中，哪些是正确的

• 实体完整性约束是一种关系模型完整性约束（正确）
• 实体完整性约束是对关系中主码属性值的约束（正确）
• 若属性$A$是关系$R$的主码属性，则属性$A$不能取空值（正确）
• 关系可以没有主码，只要有主码，则主码的值唯一（错误，在实体完整性约束中必须有主码）
• 组成主码的属性可以取空值，只要主码属性集整体不取空值即可（错误）

2. 用户自定义完整性约束由用户根据应用需要定义，反映某一具体应用所涉及的数据必须满足的语义要求。下列关于用户自定义完整性约束的描述中哪些是正确的

• 用户自定义完整性约束的实质是对属性取值进行限定，所以用户自定义完整性约束实际上就是指域完整性约束（错误）
• 域完整性约束就是指对关系中属性的取值的正确性进行限制，包括数据类型、精度、取值范围和是否允许空值等（正确）
• 域完整性约束的取值范围可分为静态定义和动态定义两种（正确）
• 域完整性约束也包括定义属性间的依赖关系，包括函数依赖和多值依赖（正确）
• 域完整性约束只能在单个关系上限定，不能涉及多个关系（错误）

3. 有一个关系：学生（学号，姓名，系别），规定学号的值域是$8$G个数字组成的字符串，这一规则属于什么约束（C)

A. 实体完整性约束
B. 参照完整性约束
C. 用户自定义完整性约束
D. 关键字完整性约束

4. 下列关于关系模型的实体完整性约束的描述中，哪个是正确的（C)

A. 若属性$A$是关系$R$的主属性，则属性$A$不能取空值
B. 若属性$A$是关系$R$的主码，则属性$A$不能取空值
C. 若属性$A$是关系$R$的主码，则关系$R$中除属性$A$之外的属性都允许取空值
D. 组成主码$A$的属性都不能取空值，而不仅仅是主码属性集整体不能取空值

5. 下列关于参照完整性约束的叙述中，哪些是正确的

• 参照完整性约束是一种关系模型完整性约束（正确）
• 在关系模型中实体及实体间的联系都是用关系来描述的，于是就自然存在着关系与关系之间的参照关系（正确）
• 两个或两个以上的关系间可以存在参照关系（正确）
• 同一个关系的属性间不可能存在参照关系（错误）
• 参照关系中每个元组在外码属性上的取值只能等于所对应的被参照关系中某个元组的主码值（错误）