13 MySQL 从入门到精通——数据完整性约束

数据完整性约束：数据库中有很多数据，有些时候我们需要对这些数据进行限制，比如说这个数据的类型，例如时间类型如果你插入一个整数是不规范的；例如一些整型你插入的是字符串就会报错，等等这些我们都需要对数据进行约束，否则数据库中就会插入一些垃圾数据

定义完整性约束
	实体完整性：
		实体（Entity）：是一个数据对象，是指客观存在并可以相互区分的事物，如一个教师、一个学生或一个雇员等。一个实体在数据库中表现为表中的一条记录。通常情况下，它必须遵守实体完整性规则。
		实体完整性规则（Entity Integrity Rule）是指关系的主属性，即主码（主键）的组成不能为空，也就是关系的主属性不能是空值（NULL）。关系对应于现实世界中的实体集，而现实世界中的实体是可区分的，即说明每个实例具有唯一性标识。
		实体完整性：可以通过两方面进行约束
			主键约束：
				1. 每一个表只能定义一个主键。
				2. 唯一性原则。主键的值，也称键值，必须能够唯一标识表中的每一行记录，且不能为NULL。也就是说一张表中两个不同的行在主键上不能具有相同的值。
				3. 最小化规则。复合主键（多个字段组成）不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的。
				4. 一个列名在复合主键的列表中只能出现一次。

				示例1：
					create table tb_user(
						id int auto_increment primary key,
						user varchar(30) not null,
						password varchar(30) not null,
						createtime datetime
					);
				示例2
					create table tb_student (
						id int auto_increment,
						name varchar(30) not null,
						sex varchar(2),
						classid int not null,
						birthday date,
						PRIMARY KEY (id,classid)
					);

				候选键约束：
					1. 候选键可以是表中的某一列，也可以是表中多个列所构成的一个组合。
					2. 候选键的值必须是唯一的，且不能为空（NULL）。
				主键约束与候选键约束区别：
					一个表中只能创建一个主键但是可以创建多个候选键
					定义主键时系统会自动创建primary key，定义候选键时系统会自动unic索引
					
					示例
						create table tb_user1(
							id int auto_increment UNIQUE,
							user varchar(30) not null UNIQUE,
							password varchar(30) not null,
							createtime TIMESTAMP default CURRENT_TIMESTAMP
						);

参照完整性：就是定义外码（外键）和主码（主键）之间的引用规则，它是对关系间引用数据的一种限制。用户定义完整性，在MySQL中目前只有InnoDB引擎类型支持外键约束
	设置外键时，通常需要遵守以下规则
		被参照表必须是已经存在的，或者是当前正在创建的表。如果是当前正在创建的表，也就是说，被参照表与参照表是同一个表，这样的表称为自参照表（Self-referencing Table），这种结构称为自参照完整性（Self-referential Integrity）。
		必须为被参照表定义主键。
		必须在被参照表名后面指定列名或列名的组合。这个列或列组合必须是这个被参照表的主键或候选键。
		外键中列的数目必须和被参照表中的列的数据相同。
		外键中列的数据类型必须和被参照表的主键（或候选键）中的对应列的数据类型相同。
		尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
	
		Usage：
			[CONSTRAINT <symbol>] FOREIGN KEY (index_col_name,...) reference_definition
				reference_definition用法:主要定义外键所参照的表、列、参照动作的声明和实施策略等4部分：
					references tbl_name[(index_col_name,...)] #参考的表、列
					[ON DELETE reference_option]
					[ON UPDATE reference_option]
						index_col_name用法:
							col_name[(length)] [ASC|DESC]
						reference_option用法:指定参照完整性约束的实现策略
							restrict:限制策略，当要删除被参照表中的被参照的列上、在外键中出现的值时，拒绝操作
							cascade：级联策略，从被参照表中删除或更新数据时，自动删除或更新参照表中数据
							set null：置空策略，从被参照表中删除或更新数据时，设置参照表中对应外键值为NULL，前提这一列没有声明not null
							no action：不采取实施策略，等同于resirict
							
		
		示例：创建两个表，其中tb_student1表中的外键classid指向tb_class的id，因此此规则设置后tb_student1想要插入数据时classid的值必须在tb_class表的id中存在否则会报错，同样如果想要直接删除tb_class表中的数据，那么在tb_student1表中的classid必须没有与tb_class的id与之对应的只，不然需要先删除tb_student1表中的classid的相应的值
			CREATE TABLE tb_class(
				id int(11) NOT NULL AUTO_INCREMENT,
				name varchar(45) NOT NULL,
				PRIMARY KEY (`id`)
			);

			create table tb_student1 (
				id int auto_increment,
				name varchar(30) not null,
				sex varchar(2),
				classid int not null,
				birthday date,
				remark varchar(100),
				primary key (id),
				FOREIGN KEY (classid)	#设置外键为ckassid
				REFERENCES tb_class(id)	#指向tb_class表中的id字段
				ON DELETE RESTRICT	#删除限制
				ON UPDATE RESTRICT	#修改限制
			);

用户定义完整性规则:是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求,主要有以下三种规则
	非空约束
	CHECK约束：如果一个字段有一定的范围就可以用CHECK约束例如年龄约束为1-130，以下是它的两种约束
		对列实施CHECK约束
		对表实施CHECK约束
	触发器
		
		示例1：对列实施CHECK约束
			create table tb_student2 (
				id int auto_increment,
				name varchar(30) not null,
				sex varchar(2),
				age int not null CHECK(age>6 and age<18),
				remark varchar(100),
				primary key (id)
			);
							
		示例2：对表实施CHECK约束
			create table tb_student3 (
				id int auto_increment,
				name varchar(30) not null,
				sex varchar(2),
				classid int not null,
				birthday date,
				remark varchar(100),
				primary key (id),
				CHECK(classid IN (SELECT id FROM tb_class)) #或者CHECK(classid IN (1,2))，表示列表值只能是1或2
			);			
		
		
命名完整性约束
		Usage：
			CONSTRAINT <symbol> [PRIMAR KEY 短语 |FOREIGN KEY 短语 |CHECK 短语]
				#symbol：用于指定约束名称。这个名字是在完整性约束说明的前面被定义，在数据库里必须是唯一的。如果在创建时没有指定约束的名字，则MySQL将自动创建一个约束名字。

		示例：
			create table tb_student1a (
				id int auto_increment PRIMARY KEY,
				name varchar(30) not null,
				sex varchar(2),
				classid int not null,
				birthday date,
				remark varchar(100),
				CONSTRAINT fk_classid FOREIGN KEY (classid)	#约束名为fk_classid
				REFERENCES tb_class(id)
				ON DELETE RESTRICT
				ON UPDATE RESTRICT
			);

更新完整性操作
	删除完整性约束
		示例：
			alter table tb_studentla drop foreign key fk_classid;
	修改完整性约束:在mysql中完整性约束是不能直接被修改的，想要修改必须先删除在增加一个同名的约束
		alter table tb_studentla add constraint fk_classid foreign key (classid) 
		references tb_class(id)
		on delete cascade
		on update cascade
		;