数据完整性和其实现方式(约束)
数据完整性
一、数据完整性的基本概念
1、什么是数据完整性？
数据完整性是指存储在数据库中的数据要能正确反映实际情况，规定输入的数据不能是无效值、错误值或者乱码等。
2、数据完整性的类型
① 实体完整性：标识符或者主键的完整性，使其值唯一。
② 域完整性：限制类型、格式和取值范围等。
③ 引用完整性：保持原表和引用表数据的一致性。
④ 自定义完整性：用户自定义的业务规则
3、数据完整性的实现方式
实体完整性：主键约束、唯一值约束
域完整性：检查约束、默认值约束
引用完整性：外键约束
用户自定义完整性：以上约束的自由组合
4、什么是约束？
数据完整性约束也就是创建表( create table )时的约束条件，是用来限制属性或者表中数据的，也可以通过修改语句( alter table )修改约束条件。
5、约束又分为行级约束和表级约束，行级约束和表级约束本质上是一样的，只是约束的效力和范围不同。
非空约束、默认值约束、主键约束一般为行级约束；
外键约束（联合主键）是表级约束；
检查约束(check) 既可以是行级约束也可以是表级约束；
二、完整性约束
1、MySQL添加或修改约束的方式
怎样添加或修改约束？
① 创建表时添加约束
② 修改表时添加或修改约束
③ 删除表的约束
④ 查询表的约束
注意：
modify column关键字用于行级约束的添加、删除
add、drop关键字用于表级约束的添加、删除
非空约束(行级约束)
非空约束：确保当前约束的属性不为空值，非空约束只能出现在表对象的列中，是行级约束。表示该属性的取值不能为空，如果插入的数据为空，会被系统阻止插入。
-- 非空约束关键字：not null
-- 创建非空约束的语法格式：
create table <table_name>(
no int not null,
name varchar(20) not null
); -- 创建一个表，使编号和名称不能为空
INSERT INTO staff VALUES(1,'张三','男',30); -- 插入成功
-- 数据库中如果不给定属性值，属性的取值会默认为空
INSERT INTO staff(staff_name) VALUES('李四');
-- 错误代码： 1364 Field 'staff_id' doesn't have a default value
-- 对添加了非空约束的属性必须给定一个取值，没有取值则会插入失败
/*表约束的修改*/
-- 修改表中行级属性约束的一般格式：
alter table <表名> modify column <属性名> <数据类型> [约束];
alter table teachar modify column t_name varchar(20) not null;
-- 通过修改表去掉非空属性约束：
alter table teachar modify column t_no varchar(20);
-- 在修改表时不添加非空约束就是去掉非空约束
-- 注意：修改表属性的时候，会对原表中的数据进行一个检查，如果出现取值冲突，则无法修改表约束。
唯一约束(表级约束)
指定某列或者某几列数据的取值不能重复。
唯一约束关键字：unique
-- 如果属性已经存在重复的值，则不能添加唯一约束。
-- ① 创建表时添加唯一约束
create table teacher_tb(
t_no int unique, -- 唯一约束
t_name varchar(20));
-- ② 指定列添加唯一约束：
alter table <表名> add 约束类型(属性名);
ALTER TABLE staff ADD UNIQUE(staff_id);
-- 示例：可以通过constraint给约束取别名
alter table <表名> add constraint <约束名> unique(属性名);
-- ③ 删除唯一约束：
alter table <表名> drop index <属性名/约束名>;
ALTER TABLE staff DROP INDEX staff_id;
复合唯一约束(表级约束)
创建复合唯一约束（表级约束）
可以对多个列进行唯一约束，约束两个以上的字段（属性）不能同时一致。
-- ① 在创建表时添加复合唯一约束
constraint <约束名> unique(<属性1>[,属性2][,…属性n])
CREATE TABLE staff(
staff_id INT NOT NULL, -- 工号(不为空）
staff_name VARCHAR(30), -- 姓名
staff_gender VARCHAR(4), -- 性别
staff_age INT,
CONSTRAINT S UNIQUE(staff_id,staff_name) -- 为staff_id,staff_name两个属性添加复合唯一约束
-- constraint用于给约束取别名
);
-- ② 在创建表后添加复合唯一约束
alter table <表名> add constraint <约束名> unique(<属性1>[,属性2][,…属性n]);
-- 这里也就是约束属性1、属性2到属性n中可以允许其中存在取相同值的属性，但不能同时相同，至少要有一个不同。
ALTER TABLE staff ADD CONSTRAINT S UNIQUE(staff_age,staff_name);
-- 数据库默认第一个非空且唯一的属性为主属性（主键）
-- 复合唯一约束的删除
ALTER TABLE staff DROP INDEX S; -- 根据约束名删除复合唯一约束
三、主键约束(表级约束)
主键约束就是非空约束+唯一约束，且每个表只能有一个主键约束（表级），在多列联合的主键约束时，联合主键的值不能同时重复。
-- 主键约束关键字：primaty key。
-- ① 在创建表时设置单列主键
create table <表名>(
属性名 数据类型 primary key, -- 设置主键约束
属性名 数据类型 not null )
-- ② 设置多个属性为联合主键
alter table <表名> add [constraint <约束名>] primary key(属性1[,属性2,…,属性n]);
-- ③ 删除主键约束
alter table <表名> drop primary key; -- 直接丢掉主键
-- 主键约束就是非空约束+唯一约束，非空且唯一，用于规定表格的实体完整性
-- 同一个数据表中非空且唯一属性可以有多个，但是主键约束(主属性)有且只有一个
-- 如果表中添加主键的字段已经存在空值或重复值，则不允许添加主键
ALTER TABLE staff ADD PRIMARY KEY(staff_id);
-- 联合主键约束
-- 数据表中可能存在多个属性同时作为一个主键的情况，一个主键包含多个属性的情况
-- 多列作为联合主键，其主键不能为空，且取值不能同时重复
-- 联合主键约束：非空约束+复合唯一约束
CREATE TABLE staff(
staff_id INT NOT NULL, -- 工号(不为空）
staff_name VARCHAR(30), -- 姓名
PRIMARY KEY(staff_id,staff_name) -- 为staff_id,staff_name两个属性添加联合主键约束
);
ALTER TABLE staff ADD PRIMARY KEY(staff_id，staff_name);
-- 修改表时为staff_id,staff_name两个属性添加联合主键约束
自增列约束(行级约束)
自增列就是在插入数据时，如果不规定属性的值，其值就会根据上一个属性的值自动加1。
-- 自增列关键字：auto_increment（行级约束）
-- 给一个属性添加自增列约束需要这个属性有唯一约束，一般只为主键添加自增列
-- ① 创建表时添加自增列
CREATE TABLE staff(
staff_id int PRIMARY KEY AUTO_INCREMENT, -- 工号(主键约束，自增）
staff_name VARCHAR(30), -- 姓名
staff_gender VARCHAR(4), -- 性别
staff_age INT
)AUTO_INCREMENT = 100; -- 设置自增列的初始值为100（初始值默认为0）
-- ② 添加自增列
alter table <表名> modify column <属性名> <数据类型> auto_increment = 100; -- 可以设置自增列的初始值（默认值为1）
ALTER TABLE staff MODIFY COLUMN staff_id INT AUTO_INCREMENT;
-- ③ 删除自增列
alter table <表名> modify column <属性名> <数据类型>;
-- 不给定带有自增列属性的值，其属性值会根据上一个属性值自增1
INSERT INTO staff(staff_id,staff_name) VALUES(NULL,'王五');
-- 如果给定带有自增列属性的取值，则用你给定的值
INSERT INTO staff(staff_id,staff_name) VALUES(7,'王五');
默认值约束(行级约束)
给带有默认值约束的属性添加数据时，如果没有给定属性值，会自动添加一个默认值。
-- 默认值约束关键字：default
-- ① 在创建表时设置默认值约束
create table <表名>(
name varchar(20) not null,
sex varchar(4) default '男' -- 性别默认为'男'
);
-- 添加/删除默认值约束
alter table <表名> modify column <属性名> <属性类型> [default <默认值>];
ALTER TABLE staff MODIFY COLUMN staff_gender VARCHAR(4) DEFAULT '男';
-- 由于默认值约束是行级约束，其添加、删除的基本格式与非空约束类似
ALTER TABLE staff MODIFY COLUMN staff_gender VARCHAR(4);
检查约束
检查约束就是检查列中属性的取值范围，判断字段的值是否为指定的值，如果不是就不允许数据的插入或修改。
-- 检查约束关键字：check
CREATE TABLE staff(
staff_id INT NOT NULL, -- 工号(不为空）
staff_name VARCHAR(30), -- 姓名
staff_gender VARCHAR(4) CHECK(staff_gender='男' || staff_gender='女'), -- 检查约束
staff_age INT CHECK(staff_age >=0 && staff_age <=100) -- 检查约束
);
alter table staff modify colum staff_age int CHECK(staff_age >=0 && staff_age <=100);
-- 检查年龄取值是否在0~100之间
-- 注意：MySQL不支持修改表时添加检查约束，支持在创建表时为属性添加检查约束
外键约束(表级约束)
外键约束是指一个表中的属性值参考另一个表中的主属性的值，也就是外键值参考主键值，由于外键约束是表级约束，所以在创建参考表之前要先创建被参考表。
-- ① 创建表时建立外键约束；
constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考属性名>)
-- 外键约束用于描述两个实体集之间的关系
-- 外键值参考主键值
CREATE TABLE staff_tb(
staff_id INT PRIMARY KEY AUTO_INCREMENT, -- 工号(不为空）
staff_name VARCHAR(30) NOT NULL, -- 姓名
staff_gender VARCHAR(4), -- 性别
staff_did INT , -- 部门编号(外键，参考于部门表中的部门编号)
staff_age INT,
CONSTRAINT for_sdid FOREIGN KEY(staff_did) REFERENCES department(d_id) -- 外键约束
);
-- 在创建员工表之前，需要创建被参考表(父表) 部门表
-- 创建部门表
CREATE TABLE department(
d_id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
d_name VARCHAR(40) NOT NULL, -- 部门名称
d_number INT DEFAULT 0 -- 部门员工人数
)AUTO_INCREMENT = 100;
-- ② 添加外键约束
alter table <表名> add constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考属性名>);
-- ③ 删除外键约束
alter table <表名> drop constraint <约束名>;
-- 根据约束名删除约束
级联更新、删除：
级联更新/删除是指父表中的元组更新或删除等操作对子表的处理方法，其有三种处理方法：
# 在更新/删除中（on update/on delete）
-- ① cascade：父表中的元组更新或删除，在子表中跟着更新或者删除
# 在外键约束后加上：on update cascade/on delete cascade
-- ② no action：无动作，若子表中有匹配的记录，就不允许更新/删除父表的数据(外键默认为no action)
# 在外键后加上：on update no action/on delete no action
-- ③ set null：设置为空，若子表中有匹配的记录，更新/删除父表中的数据，子表中对应数据设置为空
# 在外键约束后加上：on update set null/on delete set null
CREATE TABLE staff_tb(
staff_id INT PRIMARY KEY AUTO_INCREMENT, -- 工号(不为空）
staff_name VARCHAR(30) NOT NULL, -- 姓名
staff_gender VARCHAR(4), -- 性别
staff_did INT , -- 部门编号(外键，参考于部门表中的部门编号)
staff_age INT,
CONSTRAINT for_sdid FOREIGN KEY(staff_did) REFERENCES department(d_id) -- 外键约束
ON DELETE CASCADE ON UPDATE SET NULL -- 级联更新、删除
-- 在删除操作时存在对应数据跟着更新，在更新操作时存在对应数据设置为空
-- 可以只规定级联更新，也可以只规定级联删除，也可以都规定
-- 处理方式也可以分别设置
);
可以同时设置更新和删除所对应的操作
-- 在外键约束后加上：on update cascade on delete no action
# 父表在更新数据时，若子表中有匹配的记录，子表同步更新匹配的数据，并且若子表中有匹配的记录无法删除父表数据
示例：create table student1(
s_id int primary key,
s_name varchar(20) not null,
s_cid int not null,
s_age int,s_sex varchar(2),
constraint for_scid foreign key(s_cid) references class(class_id) on update cascade on delete set null );
# 学生表中的班级编号s_cid参考班级表中的班级编号class_id
