数据完整性tip
一、
1、概念：
数据完整性是指存储在数据库中的数据要能正确反映实际情况，规定输入的数据不能是无效值、错误值或者乱码等。
2、数据完整性的类型：
- 实体完整性：标识符或主键的完整性，使其值唯一。
- 域完整性：限制类型、格式和取值范围。
- 引用完整性：保持原表和引用表数据的一致性。
- 自定义完整性：用户自定义的业务规则。
3、MySQL常用约束
二、各类约束的实现及注意点
1、非空约束关键字：not null
# 非空约束关键字：not nul（行级约束）
-- 非空约束规定插入的属性值不能为空
create table teacher(
t_id int not null, -- 为教师编号添加非空约束
t_name varchar(20),
t_age int,
t_sex varchar(4)
);
insert into teacher(t_name,t_age,t_sex) values('小李',18,'男'); -- 错误：非空约束规定插入的属性值不能为空，如：t_id
-- 报错：Error Code: 1364. Field 't_id' doesn't have a default value 0.000 sec
insert into teacher values(1,'小王',18,'男'); -- 正确；
#以修改表结构的方式为某个属性添加非空约束
alter table teacher modify column t_name varchar(20) not null;
#以修改表结构的方式为某个属性去掉非空约束
alter table teacher modify column t_name varchar(20);
-- 注意：修改表的属性的时候，会对原表中的数据进行一个检查，如果出现取值冲突，则无法修改表约束
-- eg:如果属性的取值已经存在空值，就不允许为这个属性添加非空约束
2、唯一约束关键字：unique
#2、唯一约束关键字：unique（行级约束/表级约束）
-- 唯一约束限制表中的属性取值不能重复，可以为空（但只有一个为null）
create table teacher(
t_id int not null unique, -- 为教师编号添加非空且唯一的约束
t_name varchar(20),
t_age int,
t_sex varchar(4)
);
#以修改表结构的方式为某个属性添加唯一约束
alter table teacher add unique(t_id);
alter table teacher add constraint uni_tid unique(t_id);
-- constraint关键字给约束unique(t_id)取一个别名为 uni_tid
#以修改表结构的方式去掉某个属性的唯一约束
alter table teacher drop index uni_tid; -- 通过约束名删除约束（如果有取别名的话）
alter table teacher drop index t_id; -- 通过属性名删除约束（如果没有取别名的话）
#复合唯一约束（表级约束）：对多个列进行唯一约束
create table teacher(
t_id int not null, -- 为教师编号添加非空约束
t_name varchar(20),
t_age int,
t_sex varchar(4),
constraint uni_tidname unique(t_id,t_name) -- 对多个字段创建复合唯一约束，使字段不能同时一致
);
create table teacher1(
t_id int not null, -- 为教师编号添加非空约束
t_name varchar(20),
t_age int,
t_sex varchar(4),
unique(t_id,t_name) -- 对多个字段创建复合唯一约束，使字段不能同时一致
);
#以修改表结构的方式为多个属性添加唯一约束
alter table teacher add constraint uni_tidname unique(t_id,t_name);
-- 复合唯一约束不允许约束的属性列同时相同，至少要有一个不同
#以修改表结构的方式为多个属性删除唯一约束
alter table teacher drop index uni_tidname; -- 通过约束名删除多个属性约束（如果有取别名的话）
-- 如果属性的取值已经存在重复值，就不允许为这个属性添加唯一约束
3、主键约束：primary key（表级约束）
#3、主键约束：
-- 主键约束关键字：primary key（表级约束）
-- 主键约束 = 非空约束 + 唯一约束。 非空且唯一 || 用于规定实体完整性，唯一标识元组。
-- 主键是非空且唯一的，不允许有重复值或空值存在
-- 如果一个数据表中没有主键，那么第一个非空且唯一的属性会自动成为主键
-- 一个数据表中非空且唯一的属性可以有多个，但是主键只能有一个（有且仅有一个）
-- 定义主键时，如果表中数据存在重复或者为空，则添加主键会失败，所以应使主键数据不为空
#1、创建表时添加主键约束
create table teacher(
t_id int primary key, -- 为教师编号创建主键约束
t_name varchar(20),
t_age int,
t_sex varchar(4)
);
create table teacher1(
t_id int,
t_name varchar(20),
t_age int,
t_sex varchar(4),
primary key(t_id) -- 添加主键约束
);
#以修改表结构的方式为某个属性添加主键约束
alter table teacher add primary key(t_id);
#由于表中第一个非空且唯一的属性会默认成为主键，所以创建主键前要先删除这个属性的唯一约束
alter table teacher drop index uni_tidname;
#以修改表结构的方式 删除主键约束
alter table teacher drop primary key;
-- 由于一个表的的主键只有一个，直接丢掉即可，只要规定表名，可以不写约束名
#创建联合主键
alter table teacher add primary key(t_id,t_name);
-- 数据表中可能存在多个属性同时作为一个主键的情况，也就是一个主键包含多个属性
-- 多列作为联合主键，其主键的取值不能为空，且不能同时重复
-- 如果属性的取值已经存在空值和重复值，就不允许为这个属性添加主键约束
4、自增列：auto_increment
自增列约束关键字：auto_increment（行级约束）
#4、自增列约束auto_increment（行级约束）
-- 对添加了自增列约束的属性，如果不给定属性的取值，它就会根据上一个属性的取值自动+1
-- 给一个属性添加自增列约束时，需要这个属性已经有唯一性质的约束，一般只为主键添加自增列
#创建一个数据表，使表中某个属性成为主键，并为主键添加自增列：
create table teacher(
t_id int primary key auto_increment, -- 为教师编号创建主键和自增列约束默认从100开始
t_name varchar(20),
t_age int,
t_sex varchar(4)
)auto_increment = 100; -- 设置自增列的默认初始值从100开始
#修改表添加自增列：
alter table teacher modify column t_id int auto_increment;
#插入值
insert into teacher(t_name,t_age,t_sex) values('K',26,'男'),('S',23,'男'),('M',20,'女');
#删除自增列
alter table teacher modify column t_id int;
-- 在修改表时不添加自增列就是删除自增列
5、默认值约束：default（行级约束）
-- 插入数据时如果不给定属性的取值，那么带有默认值约束的属性会自动使用给定的默认值
-- 创建一个数据表，规定表中某个属性的默认值
create table staff( -- 员工表
staff_id int primary key, -- 工号（主键）
staff_name varchar(30), -- 员工姓名
staff_gender varchar(4) default '男', -- 员工性别（默认为'男'）
staff_age int
);
-- 以修改表结构的方式为某个主键添加默认值约束
alter table staff modify column staff_gender varchar(4) default '男';
insert into staff(staff_id,staff_name) values(4,"赵六");
-- 以修改表结构的方式去掉某个属性的默认值约束
alter table staff modify column staff_gender varchar(4);
6、检查约束：check（表级约束）
# 6、检查约束check（表级约束）
-- 检查约束用于规定和限制属性的取值范围
-- 创建一个数据表，规定表中某个属性的取值范围
create table staff( -- 员工表
staff_id int primary key, -- 工号（主键）
staff_name varchar(30), -- 员工姓名
staff_gender varchar(4) check(staff_gender = "男" or staff_gender = "女"), -- 员工性别
staff_age int check(staff_age >= 0 and staff_age <= 100)
);
create table staff1( -- 员工表
staff_id int primary key, -- 工号（主键）
staff_name varchar(30), -- 员工姓名
staff_gender varchar(4) , -- 员工性别
staff_age int,
check(staff_gender = "男" or staff_gender = "女"),
check(staff_age >= 0 and staff_age <= 100)
);
-- 以修改表结构的方式为某个属性添加检查约束
alter table staff add check(staff_age >= 0 and staff_age <= 100);
alter table staff add check(staff_gender = "男" or staff_gender = "女");
insert into staff values(5,"小花","女",28);
insert into staff values(6,"小敏","女",-1); -- 错误：不允许年龄小于0
-- 查看表约束
desc staff;
7、外键约束：foreign key（表级约束）
级联更行/删除：
7、外键约束foreign key（表级约束）
-- 外键约束用于描述两个或两个以上实体集之间的联系
-- 外键约束用于表示参考完整性，外键值参考主键值，外键不能参考其他非主属性
-- 在创建外键所在的参考表之前，需要先创建被参考表
-- 在创建员工表之前，需要先创建被参考表（父表）：部门表
create table department_tb( -- 部门表
d_id int primary key auto_increment, -- 部门编号
d_name varchar(20) not null, -- 部门名称
d_number int default 0 -- 部门员工人数
)auto_increment = 100;
-- 部门表数据的插入
insert into department_tb(d_name) values("人事部"),
("行政部"),("财务部"),("销售部"),("生产部");
-- 创建员工表
create table staff_tb( -- 员工表
staff_id int primary key auto_increment, -- 工号
staff_name varchar(30) not null, -- 员工姓名
staff_did int, -- 部门编号
staff_gender varchar(4) check(staff_gender = "男" or staff_gender = "女"), -- 员工性别
staff_age int check(staff_age >= 0 and staff_age <= 100),
constraint for_sdid foreign key(staff_did) references department_tb(d_id)
-- 外键：员工表中的部门编号参考于部门表中的部门编号
)auto_increment = 1000;
-- 插入员工信息（正确的测试数据）
insert into staff_tb(staff_name,staff_did,staff_gender,staff_age)
values("小芳",102,"女",26);
-- 插入员工信息（测试外键约束）
insert into staff_tb(staff_name,staff_did,staff_gender,staff_age)
values("小刚",105,"男",28); -- 错误：不存在105号部门
# 外键约束的级联更新/删除
-- 父表数据的更新/删除对子表数据的影响
-- 参考表和被参考表中存在相关联数据（对应的数据），就不能参数或修改被参考表（父表）中的数据
delete from department_tb where d_id = 102; -- 可以删除吗？ -- 不可以
-- 创建员工表
create table staff_tb( -- 员工表
staff_id int primary key auto_increment, -- 工号
staff_name varchar(30) not null, -- 员工姓名
staff_did int, -- 部门编号
staff_gender varchar(4) check(staff_gender = "男" or staff_gender = "女"), -- 员工性别
staff_age int check(staff_age >= 0 and staff_age <= 100),
constraint for_sdid foreign key(staff_did)
references department_tb(d_id) -- 外键：员工表中的部门编号参考于部门表中的部门编号
on update cascade on delete set null -- 设置级联更新/删除
-- on update set null -- 仅设置级联更新
-- on delete cascade -- 仅设置级联删除
)auto_increment = 1000;
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
