MySQL（五）

约束（Constraint）

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

常见的约束：

1. 非空约束（not null）：约束的字段不能为NULL

2. 唯一约束（unique）：约束的字段不能重复

3. 主键约束（primary key）：约束的字段既不能为NULL，也不能重复，简称PK

4. 外键约束（foreign key）简称FK

5. 检查约束（check）

   注意：Oracle数据库有check约束，但是MySQL没有，目前不支持该约束

非空约束 not null

drop table if exists t_user;

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

insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

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

mysql> select * from t_user;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | lisi     | 123      |
+------+----------+----------+
1 row in set (0.00 sec)

唯一性约束 unique

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

案例：给某一列添加unique

drop table if exists t_user;

create table t_user(
    id int,
   	username varchar(255) unique//列级约束
);

mysql> insert into t_user values(1,'zhangsan');
Query OK, 1 row affected (0.08 sec)

mysql> insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'//username重复了

insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);

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

案例：给两个列或者多个列添加unique

drop table if exists t_user;

create table t_user(
    id int,
    usercode varchar(255),
   	username varchar(255),
    unique(usercode,username)//多个字段联合添加约束
    					   //表级约束
);

insert into t_user values(1,'111','zs');//这里相当于111zs
insert into t_user values(2,'111','ls');//这里相当于111ls
insert into t_user values(3,'222','zs');//这里相当于222zs
									//都不一样
mysql> select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | zs       |
|    2 | 111      | ls       |
|    3 | 222      | zs       |
+------+----------+----------+
3 rows in set (0.00 sec)

insert into t_user values(4,'111','zs');
ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'

注意：not null约束只有列级约束，没有表级约束。

主键约束

drop table if exists t_user;
create table t_user(
    id int primary key,//列级约束
  	username varchar(255),
    email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');

mysql> select * from t_user;
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
+----+----------+------------+
3 rows in set (0.00 sec)

insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into t_user(id,username,email) values('jack','jack@123.com');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
主键的特点：不能为NULL，也不能重复。

主键约束相关的术语

1. 主键约束
2. 主键字段
3. 主键值

id int primary key,
主键约束：primary key
主键字段：id字段添加primary key之后，id叫做主键字段
主键值：id字段中的每一个值都是主键值

主键作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）

注意：一张表的主键约束只能有1个（必须记住）！！！

主键的分类

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

1. 单一主键（推荐的、常用的）
2. 复合主键（多个字段联合起来添加一个主键约束，不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

1. 自然主键：主键值最好就是一个和业务没有任何关系的自然数（推荐）
2. 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键、拿着身份证的身份证号码作为主键，不推荐使用。

使用表级约束方式定义主键

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

insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ww');
insert into t_user(id,username) values(4,'zl');

select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | ww       |
|  4 | zl       |
+----+----------+
4 rows in set (0.00 sec)

 insert into t_user(id,username) values(4,'zx');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

MySQL提供了主键值自增

primary key auto_increment

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,//id字段自动维护一个自增的数字，从1开始，以1递增
    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');
insert into t_user(username) values('e');
insert into t_user(username) values('f');

select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
|  5 | e        |
|  6 | f        |
+----+----------+
6 rows in set (0.00 sec)

Oracle也提供了一个自增机制：序列（sequence）对象

外键约束

外键约束相关术语

1. 外键约束：foreign key
2. 外键字段：添加有外键约束的字段
3. 外键值：外键字段中的每一个值

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

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

no(pk)	name	classno	classname
1	阿波	101	xx1班
2	波波	102	xx2班
…	…	…	…

缺点：冗余，不推荐

第二种方案：两张表（班级表和学生表）

t_class 班级表

cno(pk)	cname
101	xx1班
102	xx2班

t_student 学生表

sno(pk)	sname	cno(该字段添加外键约束fk)
1	阿波	101
2	波波	102

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

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

删除表的时候，先删除子表，再删除父表

创建数据的时候，先添加父表，再添加子表

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

以上表的建表语句如下

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

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

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

insert into t_class values(101,'xxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyy');

insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',102);

mysql> select * from t_class;
+-----+------------------+
| cno | cname            |
+-----+------------------+
| 101 | xxxxxxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyyyyy |
+-----+------------------+
2 rows in set (0.00 sec)

mysql> select * from t_student;
+------+-------+------+
| sno  | sname | cno  |
+------+-------+------+
|    1 | zs1   |  101 |
|    2 | zs2   |  102 |
+------+-------+------+
2 rows in set (0.00 sec)

insert into t_student values(3,'ww',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cqd`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`cno`))
//错误原因：cno没有103这个数据

注意：

1. 外键可以为NULL
2. 被引用的字段不一定是主键，但至少具有unique约束