【MySQL】二十五、约束（非空，唯一，主键，外键）

1. 什么是约束？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

2. 常见的约束有哪些呢？

• 非空约束(not null)：约束的字段不能为NULL；
• 唯一约束(unique)：约束的字段不能重复 ;
• 主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）；
• 外键约束(foreign key)：…（简称FK）；
• 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

3. 非空约束（not null）

我们可以创建一个字段带有not null的表：

create table t _user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);

假如我们插入不指定字段username值的记录，将会插入失败。

insert into t_user (id, password) values(1, '123');

ERROR 1364 (HY000): Field 'username' doesn't have a default value

4. 唯一性约束（unique）

唯一性约束修饰的字段具有唯一性，不能重复，但是可以为NULL;

4.1 案例：给一列（一个字段）设置唯一约束

drop table if exists t_student;
create table t_student (
 id int;
 username varchar(255) unique;
 password varchar(255)
);

插入username为liming的用户

mysql> insert into t_user values (1,'liming','123132');
Query OK, 1 row affected (0.01 sec)

再插入username为liming的用户，提示报错，因为字段username设置了唯一性约束。

mysql> insert into t_user values(2,'liming','123');
ERROR 1062 (23000): Duplicate entry 'liming' for key 'username'

字段值为NULL是可以的:

mysql> insert into t_user (id) values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user (id) values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user (id) values(4);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | liming   | 123132   |
|    2 | NULL     | NULL     |
|    3 | NULL     | NULL     |
|    4 | NULL     | NULL     |
+------+----------+----------+
4 rows in set (0.00 sec)

4.2 案例：给多列（多个字段）设置唯一约束

drop table  if exists t_user; 
create table t_student (
 id int;
 usercode varchar(255),
 username varchar(255),
 unique(usercode, username)
);

（1）以下unique(usernode, username)表示 usernode, usernode 两个字段联合起来不能唯一，两个或多个字段联合起来约束。我们通常称这种约束为表级约束。

 id int;
 usercode varchar(255),
 username varchar(255),
 unique(usercode, username)

例如，下面两条语句的usercode虽然相同，但可以正确插入。

insert into t_username valuse(1, '111', 'zhangsan');
insert into t_username valuse(1, '111', 'lisi');

（2）而以下这种方式，插入数据时，usercode和username单个字段唯一。我们通常称这种约束为列级约束。

usercode varchar(255) unique，
username varchar(255) unique

例如，下面两条语句的usercode相同，第一条语句插入成功后，第二条语句就不可以插入了。

insert into t_username valuse(1, '111', 'zhangsan');
insert into t_username valuse(1, '111', 'lisi');

注意：在非空约束中（not null）只有列级约束，没有表级约束。

5. 主键约束（primary key）

5.1 使用列级给一张表添加主键约束

drop table if exists t_user;
create table  t_user(
id int primary key,  // 列级主键
username varchar(255),
email varchar(255)
);

插入数据：

insert into t_user values (1, 'zhangsan', 'zs@123.com');
insert into t_user values (2, 'lisi', 'lis@123.com');
insert into t_user values (3, 'wangwu', 'ww@123.com');

此时表中的数据：

+----+----------+-------------+
| id | username | email       |
+----+----------+-------------+
|  1 | zhangsan | zs@123.com  |
|  2 | lisi     | lis@123.com |
|  3 | wangwu   | ww@123.com  |
+----+----------+-------------+

（1）如果我们在插入一个id为1的数据，将会报错。

insert into t_user values(1, 'zhaosi', 'zs@123.com');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

（2）如果我们不设置id的值，值插入username，email字段的值，也会报错。

insert into t_user (id, username, email) values('zhaosi', 'zs@123.com');

ERROR 1364 (HY000): Field 'id' doesn't have a default value

因此，根据以上的测试可知，id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。

主键的特点：不能为NULL，也不能重复。

5.2 使用表级给一张表添加主键约束

（1）单个字段

drop table if exists u_user(
id int,
username varchar(255),
primary key(id) // 表级约束
);

（2）多个字段

drop table if exists t_user;
create table u_user(
id int,
username varchar(255),
password varchar(255)
primary key(id, username) // 表级约束
);

5.3 主键相关的术语

主键约束： primary key

主键字段： 带有主键约束的字段，如上：id

主键值： 插入时，设置的主键字段值。如上id对应的值1，2，3

5.4 主键的特点，作用

• 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。

• 主键值是这行记录在这张表当中的唯一标识，它代表了当前这一行的完整记录。（就像一个人的身份证号码一样）。

• 一张表的主键只能有一个，可以将多个字段联合起来作为一个主键。

5.5 主键的分类

根据主键字段的字段数量来划分：

• 单一主键：推荐使用，常用的。（这种方式是推荐的）
• 复合主键：多个字段联合起来添加一个主键约束（复合主键不建议使用，因为复合主键违法三范式）

根据主键的性质来划分：

• 自然主键：主键值最好就是一个和业务没有任何关系的自然数。
• 业务主键：主键值和系统的业务挂钩，例如，使用银行卡的卡号作为主键，使用身份证号码作为主键。（不推荐使用），最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变，主键值可能也需要发生变化，但是有时候没有办法变化，这因为变化可能会导致主键值发生变化。

5.6 主键值自增（auto_increment）

mysql提供了主键值自增（非常重要）：

drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);

insert into t_user (username) values('a');
insert into t_user (username) values('b');
insert into t_user (username) values('c');
insert into t_user (username) values('d');

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
+----+----------+
4 rows in set (0.00 sec)

id字段自动维护一个自增的数字，从1开始，以1递增。

提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

6. 外键约束（foreign key）

6.1 关于外键约束的相关术语

外键约束： foreign key；
外键字段： 添加有外键约束的字段；
外键值： 外键字段中的每一个值；

6.2 什么是外键约束

我们通过一下一个业务背景来了解一下什么事外键约束

业务背景： 请设计数据库表，用来维护学生和班级的信息

（1）第一种方案：一张表存储所有数据

no(pk)				name			classno			classname
-------------------------------------------------------------------------------------------
1					zs1				101				北京大兴区经济技术开发区亦庄二中高三1班
2					zs2				101				北京大兴区经济技术开发区亦庄二中高三1班
3					zs3				102				北京大兴区经济技术开发区亦庄二中高三2班
4					zs4				102				北京大兴区经济技术开发区亦庄二中高三2班
5					zs5				102				北京大兴区经济技术开发区亦庄二中高三2班

缺点：冗余。【不推荐】

（2）两张表（班级表和学生表）

t_class 班级表：

cno(pk)		cname
--------------------------------------------------------
101		北京大兴区经济技术开发区亦庄二中高三1班
102		北京大兴区经济技术开发区亦庄二中高三2班

t_student 学生表

sno(pk)		   sname			classno(该字段添加外键约束fk)
------------------------------------------------------------
1				zs1				101
2				zs2				101
3				zs3				102
4				zs4				102
5				zs5				102

在t_student表中classno字段如果没有加外键约束，我们可以认识赋予相对的数据类型的值。但是加了外键约束后，该字段值必须来源于某个字段。

我们可以可以将以上的表的建表语句写成：

drop table if exists t_class;
drop table if exists t_student;

先创建父表t_class：

create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);

再创建子表t_student：

create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno), 
foreign key(classno) references t_class(cno)
);

插入时：先插父，再插子

insert into t_class values(101, 'xxxxxxx');
insert into t_class values(102, 'yyyyyyy');

+-----+---------+
| cno | cname   |
+-----+---------+
| 101 | xxxxxxx |
| 102 | yyyyyyy |
+-----+---------+

insert into t_student values (1, 'zs1', 101);
insert into t_student values (2, 'zs2', 101);
insert into t_student values (3, 'zs3', 102);
insert into t_student values (4, 'zs4', 102);

+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
+-----+-------+---------+

当插入一条外键字段classno不存在的值，将会报错。

insert into t_student values(7,'lisi',103);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

6.3 外键的两个特点

（1）外键值可以为NULL。

insert into t_student (sno, sname) values(7, 'lisi');

提示插入成功：

Query OK, 1 row affected (0.01 sec)

+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   7 | lisi  |    NULL |
+-----+-------+---------+

（2）外键字段引用其他表的某个字段时，被引用的字段不一定是主键，但至少具有unique约束。如果不指定唯一约束，那么字段值就可以重复，子表中外键字段值引用父表的字段值，就说不清引用的哪个值了。

如下表t_class中的con，如果不指定唯一约束，那么可以出来两个101，在t_student表中的classno就搞不清到底用的哪个101了。

t_class表：

+-----+---------+
| cno | cname   |
+-----+---------+
| 101 | xxxxxxx |
| 101 | yyyyyyy |
+-----+---------+

t_student表

+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
+-----+-------+---------+

6.4 顺序要求：

删除数据的时候，先删除子表，再删除父表。

删除表的时候，先删除子表，在删除父表。

添加数据的时候，先添加父表，在添加子表。

创建表的时候，先创建父表，再创建子表。

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。