数据库——表的约束_数据表的约束

一.约束概念

本质上是MYSQL通过限制用户操作的方式，来达到维护数据本身安全，完整性的一套方案

二.为什么要有约束？

本质上MYSQL是一套数据存储解决方案，除了解决基本的数据存储功能之外，还要保存数据尽可能的安全，减少用户误操作的可能性

三.如何约束？

约束也是一整套约束方案，主要如下：

1.空属性

（1）null(默认的)和not null(不为空)
（2）数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。
案例：

创建一个班级表，包含班级名和班级所在的教室。
如果班级没有名字，你不知道你在哪个班级
如果教室名字可以为空，就不知道在哪上课

mysql> create table myclass(
 -> class_name varchar(20) not null,
 -> class_room varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO  |   | NULL  |    |
| class_room | varchar(10) | NO  |   | NULL  |    |
+------------+-------------+------+-----+---------+-------+
//插入数据时,没有给教室数据插入失败：
mysql> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

mysql> create table tt10 (
 -> name varchar(20) not null,
 -> age tinyint unsigned default 0,
 -> sex char(2) default '男'
 -> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc tt10;
+-------+---------------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20)     | NO  |   | NULL  |    |
| age  | tinyint(3) unsigned | YES |   | 0    |    |
| sex  | char(2)       | YES |   | 男   |    |
+-------+---------------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值

mysql> insert into tt10(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt10;
+----------+------+------+
| name   | age | sex |
+----------+------+------+
| zhangsan |   0 | 男  |
+----------+------+------+
1
2
3
4
5
6
7
8

注意：只有设置了default的列，才可以在插入值的时候，对列进行省略

3.列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

mysql> create table tt12 (
 -> name varchar(20) not null comment '姓名',
 -> age tinyint unsigned default 0 comment '年龄',
 -> sex char(2) default '男' comment '性别'
 -> );
 mysql> desc tt12;
+-------+---------------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20)     | NO  |   | NULL  |    |
| age  | tinyint(3) unsigned | YES |   | 0    |    |
| sex  | char(2)       | YES |   | 男   |    |
+-------+---------------------+------+-----+---------+-------+
mysql> show create table tt12\G
*************************** 1. row ***************************
   Table: tt12
Create Table: CREATE TABLE `tt12` (
`name` varchar(20) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
`sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

4.zerofill

zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0

mysql> show create table tt3\G
***************** 1. row *****************
   Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> insert into tt3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
mysql> alter table tt3 change a a int(5) unsigned zerofill;
mysql> show create table tt3\G
*************************** 1. row ***************************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(5) unsigned zerofill DEFAULT NULL, --具有了zerofill
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> select * from tt3;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

5.主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
（1）在创建表打时候直接指定主键

mysql> create table tt13 (
-> id int unsigned primary key comment '学号不能为空',
-> name varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | | <= key 中 pri表示该字段是主键
| name | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11

（2）主键对应的字段不能重复，一旦重复，操作失败

mysql> insert into tt13 values(1, 'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt13 values(1, 'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
1
2
3
4

（3）当表创建好以后但没有主键的时候，可以追加主键

alter table 表名 add primary key(字段列表)
1

（4）删除主键

alter table 表名 drop primary key;
1

mysql> alter table tt13 drop primary key;
mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8

（5）复合主键
在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，
可以使用复合主键。

mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | | <= 这两列合成主键
| course | char(10) | NO | PRI | | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
mysql> insert into tt14 (id,course)values(1, '123');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt14 (id,course)values(1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主键冲突
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

6、自增长（设为自增长一定为主键）

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得
到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
(1)自增长的特点：
Ⅰ、任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
Ⅱ、自增长字段必须是整数
Ⅲ、一张表最多只能有一个自增长

mysql> create table tt21(
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null default ''
-> );
mysql> insert into tt21(name) values('a');
mysql> insert into tt21(name) values('b');
mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
1
2
3
4
5
6
7
8
9
10
11
12
13

（2）查看最近插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

mysql > select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1
2
3
4
5
6

注意：一般设置自增长的为主键，与业务逻辑无关的，否则一旦改变，表的改动太大

7.唯一键

（1）主键VS唯一键
Ⅰ、唯一性的数据，被选择成为主键；
Ⅱ、唯一键，目的不在于保证绝对的唯一性，在于其它非主键字段，想要一定的唯一的，则可以设置为唯一性的约束，保证数据的安全性和完整性；
Ⅲ、主键标识的更多是唯一性的，唯一键更多保证不和其它信息重复

mysql> create table student (
-> id char(10) unique comment '学号，不能重复，但可以为空',
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(id, name) values('01', 'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 01 | aaa |
| NULL | bbb |
+------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8.外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定
义外键后，要求外键列数据必须在主表的主键列存在或为null。
（1）外键存在的意义：建立表与表之间的关系
（2）外键与外键约束是不同的概念
外键：表与表之间的关联关系；
外键约束：表信息正确才能插入，确保信息的安全；
（3）先创建主键表

create table myclass (
id int primary key,
name varchar(30) not null comment'班级名'
);
1
2
3
4

再创建从键表

create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);
1
2
3
4
5
6

正常插入数据

mysql> insert into myclass values(10, '计科'),(20, '软工');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
1
2
3

mysql> insert into stu values(201, '张三', 10),(202, '李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
1
2
3

插入一个班级号为30的学生，因为没有这个班级，所以插入不成功==（外键约束）==

mysql> insert into stu values(100, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1
FOREIGN KEY (class_id) REFERENCES myclass (id))
1
2
3
4