MySQL数据完整性约束

数据完整性是指数据的正确性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。在MySQL中提供了多种完整性约束。

1、主键约束

主键可以是表中的某一列，也可以是表中的多个列所构成的一个组合；其中，由多个列组合而成的主键也称为复合主键。在MySQL中，主键列必须遵守以下规则。

（1）每一个表只能定义一个主键。

（2）唯一性原则。主键的值，也称键值，必须能够唯一表示表中的每一条记录，且不能为NULL。

（3）最小化规则。复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的。

（4）一个列名在复合主键的列表中只能出现一次。

示例：创建学生信息表tb_student时，将学号（stu_id）字段设置为主键。
 

CREATE TABLE tb_student
(
	stu_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30)
);

示例：创建用户信息表tb_student时，将学号（stu_id）和所在班级号（class_id）字段设置为复合主键。

CREATE TABLE tb_student
(
	stu_id INT AUTO_INCREMENT,
	name VARCHAR(30),
	class_id INT NOT NULL,
	PRIMARY KEY (stu_id,class_id)
);

示例：通过修改数据表结构，添加主键约束。 

ALTER TABLE tb_student ADD CONSTRAINT PRIMARY KEY(stu_id);

 

2、唯一约束（替代键约束或者候选键约束）

唯一约束使用UNIQUE关键字来定义。唯一约束的值必须是唯一的，允许为空（NULL）。

在MySQL中，唯一约束与主键之间存在以下两点区别。

（1）一个表只能创建一个主键，但可以定义多个唯一约束。

（2）定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

示例：创建用户信息表tb_student时，将学号（stu_id）和姓名（name）设置为唯一约束。
 

CREATE TABLE tb_student
(
	stu_id INT UNIQUE,
	name VARCHAR(30) UNIQUE
);

 示例：创建用户信息表tb_student时，将学号（stu_id）和姓名（name）字段设置为复合唯一约束。

CREATE TABLE tb_student
(
	stu_id INT,
	name VARCHAR(30),
	UNIQUE uniq_id_name (stu_id,name)
);

示例：通过修改数据表结构，添加唯一约束。

ALTER TABLE tb_student ADD CONSTRAINT uniq_id_name UNIQUE(stu_id,name);

3、外键约束（FOREIGN KEY）

MySQL有两种常用的引擎类型（MyISAM和InnoDB），目前，只用InnoDB引擎类型支持外键约束。

foreign key(A字段) references B(id) # A字段和外键的字段一定要保证unique

FOREIGN KEY fk_class_id (class_id)
    REFERENCES tb_class(class_id)--------参照完整性约束，定义外键连接到是哪个表的哪个主键。

示例：创建班级信息表（tb_class）和学生信息表（tb_student），并设置学生信息表中班级编号（class_id）字段的外键约束。

-- 创建班级信息表
CREATE TABLE tb_class
(
	class_id INT AUTO_INCREMENT PRIMARY KEY,
	class_name VARCHAR(30) NOT NULL
);
 
-- 创建学生信息表，并设置班级ID的外键约束
CREATE TABLE tb_student
(
	stu_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	class_id INT NOT NULL,
	FOREIGN KEY fk_class_id (class_id)
	REFERENCES tb_class(class_id)
);

示例：通过修改数据表结构，添加外键约束。

ALTER TABLE tb_student ADD CONSTRAINT FOREIGN KEY fk_class_id (class_id) REFERENCES tb_class(class_id);

4、检查约束

检查约束用来指定某列的可取值的范围，它通过限制输入到列中的值来强制域的完整性。

注意：目前的MySQL版本只是对CHECK约束进行了分析处理，但会被直接忽略，并不会报错。

示例：创建学生信息表tb_student时，将年龄（age）的值设置在7至18之间（不包括18）的数值。

CREATE TABLE tb_student
(
	stu_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	age INT NOT NULL CHECK(age>=7 AND age<18)
);

5、自动增长约束（auto_increment）

定义：自动增长，通常搭配主键字段使用，只能用于整形。

create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

6、约束的删除

删除约束语法：

ALTER TABLE 表名 DROP [FOREIGN KEY| INDEX 约束名称]|[PRIMARY KEY]

示例：删除约束。

CREATE TABLE tb_student
(
	stu_id INT,
	name VARCHAR(30) ,
	class_id INT NOT NULL,
 
	-- 主键约束
	PRIMARY KEY(stu_id),
 
	-- 外键约束
	FOREIGN KEY fk_class_id (class_id)
	REFERENCES tb_class(class_id),
 
	-- 唯一性约束
	UNIQUE uniq_name (name)
);
 
-- 删除主键约束
ALTER TABLE tb_student DROP PRIMARY KEY;
 
-- 删除外键约束
ALTER TABLE tb_student DROP FOREIGN KEY fk_class_id;
 
-- 删除唯一性约束
ALTER TABLE tb_student DROP INDEX uniq_name;