四、MySQL数据完整性

数据完整性是为了保证插入到数据库中的数据是正确的,防止用户可能的错误输入。数据完整性分为实体完整性、域完整性、参照完整性。

实体完整性

• 实体完整性中的实体指的是表中的行,因为一行记录对应一个实体
• 实体完整性规定表的一行在表中是唯一的实体,不能出现重复
• 实体完整性通过表的主键来实现
  • 主键关键字: primary key
  • 主键自动增长关键字: auto_increment
  • 主键特点: 不能为null，并且唯一
  • 主键分类:
    逻辑主键:例如ID,不代表实际的业务意义,只是用来唯一标识一条记录(推荐)
    业务主键:例如username，参与实际的业务逻辑。
• 主键使用方式

create table table_name(
			id int primary key auto_increment,
			name varchar(100)
);

域完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束

• 常见数据类型，见 https://blog.csdn.net/ybw_2569/article/details/108106455
• 长度：存储数据的字节
• 非空约束：NOT NULL 不开为空
• 唯一约束：UNIQUE 不可重复

CREATE TABLE table_name(
			username varchar(100) NOT NULL UNIQUE,
			gender varchar(100) NOT NULL,
			phonenum varchar(100) UNIQUE
	);

参照完整性

参照完整性指的就是多表之间的设计,主要使用外键约束。

一对一

实际开发中直接设计一张表格就可以了。不会按照下面的进行设计。

• 按照外键关联，在IdCard表中的外键添加唯一约束
  

• 按照主键关联，对主键添加外键约束

一对多

通过外键进行连接，一般创建在关系多的表中，也就是一对多中的多。

• 业务信息
  客户和订单的关系就是一对多，一个客户可以有多张订单,一张订单属于一个客户
  
• 代码实现

# 创建客户表:
CREATE TABLE customers(
	id int,
	name varchar(100),
	address varchar(255),
	PRIMARY KEY(id)
);
# 创建订单表:
CREATE TABLE orders(
	order_num int primary key,
	price float(8,2),
	status int,
	customer_id int,
	CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)
);
# 外键约束: 
# constraint customer_id_fk foreign key(customer_id) references customers(id);
# 注: constraint: 约束的意思。foreign key: 外键。references: 参照
#创建一个名叫customer_id_fk的外键约束,其中外键指的是customer_id,并且参照的是 customers表中的id列。

多对多

同样是通过外键进行连接的，一般是创建第三张表，在第三张表中进行关联。

• 业务信息
  老师和学生是多对多关系, 一个老师对应多个学生，一个学生被多个老师教。

• 代码实现

# 创建老师表:
Create table teachers(
		id int,
		name varchar(100),
		salary float(8,2),
		primary key(id)
);

# 创建学生表:
Create table students(
	id int,
	name varchar(100),
	grade varchar(100),
	primary key(id)
);	

# 第三张表格：
Create table teacher_student(
	t_id int,
	s_id int,
	primary key(t_id,s_id),
	CONSTRAINT teacher_id_fk FOREIGN KEY(t_id) REFERENCES teachers(id),
	CONSTRAINT student_id_fk FOREIGN KEY(s_id) REFERENCES students(id)
);

级联删除与更新

MySQL利用外键级联删除、更新

MySql支持外键的存储引擎只有InnoDB
在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
在创建子表索引的时候，可以指定在删除、更新父表时，对子表进行相应的操作，包括

• RSTRICT 主表约束策略
• NO ACTION 无动作策略
• SET NO 置空策略
• CASCADE 级联策略

其中RSTRICT 和 NO ACTION 相同：在子表有关联记录的情况下父表不能更新
CASCADE:父表在跟新或者删除时，更新或者删除子表对应的记录
SET NO:表示父表在更新或者删除的时候，子表对应的字段被SET NO

设置方式

• 在创建的时候添加

在创建主外键的时候要加上 ON DELETE CASCADE 这个关键字，就可以实现级联删除

• 在已创建表中添加

alter table sd add constraint sd_supervisor_fk foreign key (SupId) references supervisor (SupId) ON DELETE CASCADE;