MySQL的约束与设计

1、数据库表的约束

1.1、基本概述

约束的作用：
  对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束的种类：

约束名	关键字	说明
主键	primary key	非空且唯一
唯一	unique	这一列不能有重复值
非空	not null	这一列必须有值
外键	foreign key	主表中主键列，在从表中外键列
默认	default	如果一列没有值，使用默认值
检查约束	check	（MySQL不支持）

1.2、主键约束

主键的作用： 用来唯一标识数据库中每一条记录。

需要注意的是，被主键标识的字段（列），其值非空且唯一，一张表中只能有一个字段为主键。

（1）在创建表时，添加主键约束：

create table 表名(
	字段名1 int primary key,-- 添加主键约束
	字段名2 varchar(20)
);

（2）创建表之后，添加主键约束：

ALTER TABLE 表名 MODIFY 字段 INT PRIMARY KEY;

（3）删除主键：

-- 错误写法：alter table 表名 modify 字段 int ;
ALTER TABLE 表名 DROP PRIMARY KEY;

（4）自动增长： 如果主键是数值类型的，使用auto_increment可以来完成值的自动增长，默认值为1。注意，自增长只能用在主键上。

-- 在创建表时，添加主键约束，并且添加自增长约束
create table 表名(
	字段名1 int primary key auto_increment,-- 给id添加主键自增长约束
	字段名2 varchar(20)
);

-- 在创建表之后，添加自增长约束
ALTER TABLE 表名 MODIFY 字段 INT AUTO_INCREMENT;

-- 删除自动增长
ALTER TABLE 表名 MODIFY 字段 INT;

1.3、唯一约束

唯一约束的作用： 标识某一列中的值不能重复。

需要注意的是，在MySQL中，唯一约束限定的列值可以存在null，即多个null值不受唯一约束影响。

（1）在创建表时，添加唯一约束：

create table 表名(
	字段名1 int unique, -- 添加唯一约束
	字段名2 varchar(20)
);

（2）在创建表之后，添加唯一约束：

ALTER TABLE 表名 MODIFY 字段 类型 UNIQUE;

（3）删除唯一约束：

ALTER TABLE 表名 DROP INDEX 字段;

1.4、非空约束

非空约束的作用： 标识某一列中的值不能为null。

（1）在创建表时，添加非空约束：

create table 表名(
	字段名1 int, 
	字段名2 varchar(20) NOT NULL -- 添加非空约束
);

（2）在创建表之后，添加非空约束：

ALTER TABLE 表名 MODIFY 字段 类型 NOT NULL;

（3）删除非空约束：

ALTER TABLE 表名 MODIFY 字段 类型;

1.5、外键约束

外键约束的作用： 让表于表产生关系，从而保证数据的正确性。

（1）在创建表时，添加外键约束：

create table 表名(
	-- 省略其他字段
	外键字段 int, 
	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
	-- 外键名称自定义，不要重复即可。后面删除外键约束需要用到此外键名称。
);

（2）在创建表之后，添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

（3）删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

（4）级联操作： 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

-- 添加级联更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;

-- 添加级联删除，谨慎使用！
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE;

-- 添加级联更新和删除，谨慎使用！
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

2、数据库的设计

2.1、多表之间的关系

关系	说明	关系的维护
一对多	最常用的关系，例如部门和员工，一个部门有多个员工，但一个员工只有一个部门	主外键的关系
多对多	例如选课表和学生表，一门课程可以有多个学生选择，一个学生也可以选择多门课程	多个中间表，两个一对多
一对一	相对使用较少，例如一个公民只有一个身份证号	–

2.2、数据规范化

2.2.1、三大范式

  什么是范式？ 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

  其实目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF） 、第四范式(4NF）和第五范式（5NF，又称完美范式）。
  满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ，其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

范式	特点
1NF	原子性：表中每列不可再拆分。
2NF	不产生局部依赖，一张表只描述一件事情。
3NF	不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。

2.2.2、1NF

  数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。例如下面的班级表：

2.2.3、2NF

  在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
  所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。
  第二范式的特点：一张表中只描述一件事情，表中的每一列都完全依赖于主键。

示例：
（1）借书证表：

（2）分成两张表：

2.2.4、3NF

  在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。
  简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
示例：
（1）学生信息表：

（2）存在传递的决定关系：即学号→所在学院→学院地点，拆分成两张表：