添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
一身肌肉的山楂  ·  Analytics | Business ...·  2 月前    · 
眉毛粗的油条  ·  mongodb嵌套查询子集合，如何过滤掉不符 ...·  2 月前    · 
有情有义的卤蛋  ·  帮助中心-SPSSPRO-简单易懂的数据分析 ...·  4 月前    · 
温文尔雅的蛋挞  ·  我为什么从Redux迁移到了Mobx·  8 月前    · 
长情的小熊猫  ·  A guide to ...·  11 月前    · 
link管理  ›  数据完整性和其实现方式(约束) - 宣哲
https://www.cnblogs.com/Mr-black-7/p/16376776.html
俊秀的跑步机
3 月前

数据完整性是指存储在数据库中的数据要能正确反映实际情况，规定输入的数据不能是无效值、错误值或者乱码等。

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
 
推荐文章
一身肌肉的山楂  ·  Analytics | Business intelligence and reporting | CWT
2 月前
眉毛粗的油条  ·  mongodb嵌套查询子集合，如何过滤掉不符合条件的子集数据 - CSDN文库
2 月前
有情有义的卤蛋  ·  帮助中心-SPSSPRO-简单易懂的数据分析工具使用教程
4 月前
温文尔雅的蛋挞  ·  我为什么从Redux迁移到了Mobx
8 月前
长情的小熊猫  ·  A guide to international transfers | ICO
11 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台