详解MySQL完整性约束

完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不再执行用户的操作。MySQL 完整性约束常用的操作有六大操作：

1. 主键约束
2. 唯一约束 unique
3. 非空 not null
4. 默认值约束(default)
5. 自动增长约束(auto_increment)
6. 外键约束(foreign key)

下面我们一一介绍一下：

主键约束 primary key

主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

特点：

• 唯一且不为空
• 主键可以由一个字段组成，也可以由多个字段组成
• 如果主键可以由一个字段组成既可以添加到列级也可以添加到表级，但是如果由多个字段组成只能添加到表级

使用：

单字段主键添加可以定义的时候跟在后面，也可以在定义完成以后跟在最后：

CREATE TABLE user(
    id INT PRIMARY KEY, ......
);

CREATE TABLE user(
    id INT , ......
PRIMARY KEY(id）
);

多字段主键

CREATE TABLE user( 
    id INT ,
    name VARCHAR(50) ,
    ......                    
[CONSTRAINT id_name]PRIMARY KEY(id, name)
);

给已有表添加主键

语法：alter table 表名 add [constraint 主键约束名] primary key(字段名);

CREATE TABLE user(
	id INT,
	name VARCHAR(50),.....
);

ALTER TABLE user ADD CONSTRAINT constraint  PRIMARY KEY(id);

自增键约束 auto_increment

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。

特点：

• 当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID,自动增长默认的初始值1，每增加一条记录，该字段的值会增加1;
• 一个数据库表中只能有一个字段使用该约束;
• 配合主键一起使用，该字段的数据类型必须是整数类型。
• 由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。
• mysql8 中,AUTO_INCREMENT 必须设为键(主键、外键和唯一键均可)！ 否则会报错！

使用：

CREATE TABLE user(
    id INT NOT NULL AUTO_INCREMENT, ......
);

给已有表添加自动增长约束

语法：alter table 表名 modify 字段名 字段类型 auto_increment;

删除自增长约束

语法：alter table 表名 modify 字段名 字段类型;

唯一键约束 unique

特点：

• 可以取空值
• 不能重复
• 一个表里可以有多个唯一键。

使用：

一个唯一键：

CREATE TABLE user( 
    id INT ,
    name VARCHAR(50) UNIQUE ,
    ......                    
);

多个唯一键：

create table user(
	id int(8),
	name varchar(20),
	age int(2),
	sex varchar(1),
	constraint uk_user_id_name unique(id,name)
);

给已有表添加唯一约束

语法：alter table 表名 add [constraint 唯一约束名] unique(字段1,字段2...);

删除唯一约束

语法：alter table 表名 drop index 唯一约束名;

非空约束 not null

当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用not null约束进行设置。not null约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。

使用：

CREATE TABLE user(
    id INT NOT NULL , ......
);

默认值约束 default

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字DEFAULT来设置。

使用：

CREATE TABLE user(
    tickname VARCHAR(50) DEFAULT ‘小美’, ......
);

给已有表添加默认值约束

语法：alter table 表名 modify 字段名 字段类型 default value;

删除默认值约束

语法：alter table 表名 modify 字段名 字段类型;

特点：

1. 只能使用列级约束。
2. 对于使用默认值约束，如果插入的数据为 “null”，则不会使用默认值，只有没有插入数据时候，才会使用默认值。

外键约束 foreign key

外键是表的一个特殊字段，外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。

设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，两张表有关联关系，例如学生信息表（父表），考试信息表（子表）；里面子表里面有张三的考试信息，它关联到了父表里面，要删除要一起删除，否则会有脏数据。

使用：

CREATE TABLE user( 
    id INT ,
    name VARCHAR(50) ,
    ......                    
[CONSTRAINT fk_name] FOREIGN KEY(id) REFERENCES formation(score)
);

其中,user参数是要设置外键的表名，id参数是要设置外键的字段，formation是父表的名称，score是父表中设置主键约束的字段名。

注意：目前外键，存储函数，存储过程，触发器…这些在后台开发过程中基本是不用的，因为这些限制逻辑或者代码逻辑是由mysql本身控制的，一个后端服务器的性能首先到瓶颈的是存储层模块，所以要把核心逻辑给mysql作，各个表的关系要放到业务层，不给mysql增加负担，外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。

列级约束和表级约束

（1）对一个数据列建立的约束，称为列级约束

（2）对多个数据列建立的约束，称为表级约束

（3）列级约束既可以在列定义时声明，也可以在列定以后声明

（4）表级约束只能在列定义后声明