MySQL 约束的使用

1 数据完整性的分类

数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。数据完整性因此而提出。
数据完整性=精确性+可靠性

1.1实体完整性

实体完整性要求表中的所有行都有一个唯一标识符。每一行都是唯一的。这个唯一标识符可能是一列，也可能是几列的组合，称为主键。也就是说，表中的主键在所有行上必须取唯一值。

主键为员工编号，新增记录的员工编号和表中重复，不能加入表。

1.2 域完整性

是指一个列的输入有效性，是否允许为空值。强制域完整性的方法有：限制类型、格式或可能值的范围。
新增的记录员工编号值不符合要求。

1.3参照完整性

是指保证主关键字和外部关键字之间的参照关系。
它涉及两个或两个以上表数据的一致性维护。外键值将引用表中包含此外键的记录和被引用表中主键与外键相匹配的记录关联起来。在输入、更改或删除记录时，参照完整性保持表之间已定义的关系，确保键值在所有表中一致。

新增的记录中部门号T01，和右边表的主键（部门编号）对应不上

2 完整性约束

2.1 MySQL常见的完整性约束（不区分大小写）

• primary key 主码约束（主键）
• unique 唯一性约束
• not null 非空值约束
• auto_increment 用于整数列默认自增加1
• unsigned 无符号整数
• DEFAULT default_value默认值约束
• foreign key外键约束
• DEFAULT cur_timestamp 创建新记录时默认保存当前时间（仅适用timestamp数据列）
• ON UPDATE cur_timestamp 修改记录时默认保存当前时间（仅适用timestamp数据列）
• CHARACTER SET name 指定字符集（仅适用字符串）

2.2 空与非空

某数据单元的值为空表示该单元是以空值 (NULL) 作为其数据。NULL 不等于零或空白字符串：它表示没有输入任何内容，或提供了一个显式 NULL 值，通常表示该值未知或不适用。

如果不允许该列的值为空，其定义的关键字为NOT NULL。

例：如果不允许员工信息表中员工姓名列的值为空，则在创建表语句的列定义中加入NOT NULL 关键字即可：

CREATE TABLE EMPLOYEE( 
emp_id int PRIMARY KEY,   #主键
dep_id CHAR(3),
emp_name VARCHAR(20) NOT NULL  # not null 不允许emp_name列的值为空
 );

2.3 主键

表中的一个列或多个列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键。主键可通过 PRIMARY KEY约束来创建。

• 选择主键的要求
  1）主键列值必须非空
  2）一个表中只能有一个主键
  3）单个列作为主键时，列的值必须唯一
  4）多个列作为主键时，允许某个列的值存在重复，但作为主键的列的组合的值必须唯一最少性
  5）尽量选择单个键作为主键稳定性
  6）尽量选择数值更新少的列作为主键

• 创建主键

CREATE TABLE EMPLOYEE(
emp_id  int PRIMARY KEY, 
dep_id int, 
emp_name VARCHAR(20) NOT NULL, 
emp_gender BIT NOT NULL
);

• 复合主键

CREATE TABLE `employee` (
`id` int(11) NOT NULL, 
`emp_name` varchar(20) DEFAULT NULL, 
`emp_age` int(11) NOT NULL,
`emp_add` varchar(50) DEFAULT NULL, 
`emp_salary` decimal(8,2) DEFAULT NULL, 
`create_at` date DEFAULT NULL, 
PRIMARY KEY (id,emp_name)       #复合主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.4 标识列

AUTO_INCREMENT 自动标识列，在需要产生唯一标志符号或者顺序值时候，可用此属性。值一般从1开始，每行增加1，在插入NULL到一个AUTO_INCREMENT列时，MySQL会插入一个比该列中当前最大值大1 的值，一个表中最多能有一个有此属性的列。对于想使用此属性的列应该定义为NOT NULL，并定义为PRIMARY KEY 或者定义为UNIQUE键。

create table t(   
id int auto_increment primary key  #这里原本标识列必须非空要加not null，但加主键约束也可以，因为主键必须是非空的。
);

获取当前数据库表的自增字段

select last_insert_id();

2.5 UNIQUE

唯一性，在插入数据时设置UNIQUE的列的值不能出现重复，有以下特性：
一张表可以设置多列为UNIQUE
设置UNIQUE的列允许有一项为null (对于mysql5.1之后不适应，5.1之后不支持引擎BDB )

CREATE TABLE `employee` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`emp_name` varchar(20) ,  
UNIQUE(`emp_name`),  
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8

2.6 默认值

可以为列设定某一默认值，当记录在插入时未显式提供值的情况下，列的值会使用它的默认值，不可为标识列设定默认值。(比如名字这种一般就是标识列，不能默认)
默认值可以是常量（数字，字符串），也可以是系统函数，也可以是NULL

CREATE TABLE EMPLOYEE( 
emp_id  CHAR(8) PRIMARY KEY, 
dep_id  CHAR(3), 
emp_name  VARCHAR(20) NOT NULL, 
emp_address  VARCHAR(100) DEFAULT '地址不详'  #DEFAULT 默认值
);

2.7 外键

外键是用于建立和加强两个表数据之间关联性的约束。
通过在数据表中保存相关实体记录的主键，可创建两个表之间的关联。在记录中添加的相关实体的主键信息（唯一标识）就称为“外键”，例如在员工信息表中保存的部门编号、在订单信息表中保存的产品编号等等。

• 主键PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

create table student( 
sid int not null auto_increment, 
name varchar(20) not null, 
primary key(sid) #PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
);

• 外键（自动检查外键是否匹配，仅适用InnoDB）

create table score( 
cid int not null auto_increment primary key, 
score int, sid int, 
foreign key(sid) references student(sid)  
#foreign key(sid) references student(sid)，需要单独一行，不要直接放到 sid int 后面
#MyISAM引擎表不支持外键
);

2.8 主表和从表

• 当主表中没有对应的记录时，不能将记录添加到子表
  例：成绩表中不能出现在学员信息表中不存在的学号；

• 不能更改主表中的值而导致子表中的记录孤立
  例：把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；

• 子表存在与主表对应的记录，不能从主表中删除该行
  例：不能把有成绩的学员删除了

• 删除主表前，先删子表
  例：先删学员成绩表、后删除学员信息表

3 更改表约束

3.1 添加主键约束

alter table 表名 add （constraint 主键 （形如：PK_表名）） primary key 表名(主键字段);

ALTER TABLE person ADD PRIMARY KEY(id);

3.2 添加外键约束

alter table 从表 add constraint 外键（形如：FK从表主表） foreign key 从表(外键字段) references 主表(主键字段);
alter table score add constraint fk foreign key(stu_id) references student(id);

3.3 删除主键约束

alter table 表名 drop primary key;
ALTER TABLE person DROP PRIMARY KEY;

3.4 删除外键约束

alter table 表名 drop foreign key 外键（区分大小写）;
alter table person drop foreign key fk;  

3.5 修改主键

alter table person drop primary key;--先删除主键   
alter table person add primary key(id);--然后再添加主键

3.6 删除被其他关联的父表

1.先删除子表的外键约束，然后再删除附表；
2.先删除子表，再删除父表；

3.7 删除外键的方法

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
ALTER TABLE person DROP FOREIGN KEY d_FK;

3.8 修改表的其他选项

alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的压缩类型