【MySQL】第五章-表的约束

发表于 | 分类于
字数统计: 2.6k

第五章 表的约束

  在之前的章节我们有提到过约束，所谓约束是在建表时对表的某一字段进行的特殊限定或者特殊功能附加，在MySQL中有着广泛应用。

空属性

  首先要说的的是在MySQL中，空也用null进行表示，我们可以看到在很多数据暂未赋值时，它们的默认值会为空，不过MySQL中的null与C语言中的NULL有着较大区别。在C语言中空表示着值为0或者不存在值等等意思，而在MySQL中空表示着不知道此处的值，不确定此处的值，并且空不能直接参与运算，因此我们在建表时一般要求值都不为空。因此我们需要在字段后加上not null约束。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    ->     id int not null,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

  由此即可看出通过我们添加了not null约束，id字段已经要求不能为空了，那么如果我们在添加数据时没有给id字段值会出现什么情况呢？

1
2
MariaDB [student]> insert into student(name) values("张三");
ERROR 1364 (HY000): Field 'id' doesn't have a default value

  当我们在要求id不能为空但是又没有为它设置其他默认值时就会报错。

默认值

  我们可以给某一字段设置默认值，一般情况下默认值为null，要想设置默认值利用default约束即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    ->     id int not null default 1, 
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 1       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  在此次建表时我们要求id字段不为空，并且默认值设置为1，我们再插入一条数据看看。

1
2
3
4
5
6
7
8
9
10
MariaDB [student]> insert into student(name) values("张三");
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

  这次我们同样没有给id赋值，但是根据默认值它的值变为了1。

列描述

  所谓描述是添加一种类似于注释一样的约束，实际上不会对字段产生任何影响。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    ->     id int not null default 1 comment '编号', 
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.08 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 1       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  这样就已经为id字段添加了注释，但是我们发现查看表信息里面却没有显示，这是因为这个注释只会在我们使用查看建表信息时才会显示。

1
2
3
4
5
6
7
8
9
10
MariaDB [student]> show create table student;
+---------+----------------------------------------------------------------+
| Table   | Create Table                                                   |
+---------+----------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL DEFAULT '1' COMMENT '编号',
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+---------+----------------------------------------------------------------+
1 row in set (0.01 sec)

  这样我们以后我们可以给每个字段都加上描述，对表字段信息做进一步描述。

zerofill

  这个约束是填充约束，对于字段空位用0进行填充。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [student]> create table student (
    ->     id int(5) zerofill default 1 comment '编号', 
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [student]> insert into student values(1,"张三");
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from student;
+-------+--------+
| id    | name   |
+-------+--------+
| 00001 | 张三   |
+-------+--------+
1 row in set (0.00 sec)

  在我们添加zerofill字段后根据我们对字段类型大小对所有空位进行了填充0的操作。

主键

  主键是约束中十分重要的约束，主键作为一种索引，可以用于对数据的排列查找等，因此一个表中最多只能拥有一个主键，同时因为一个键值就唯一了一个数据因此主键不能为空同时不能重复。因此它自带not null约束。我们对有个字段使用primary key约束将其约束为主键，通常主键选做使用int类型的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    ->     id int(5) primary key default 1 comment '不能为空',
    ->     name varchar(20) not null comment '姓名'
    -> );
Query OK, 0 rows affected (0.02 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | 1       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

  这样我们就把id字段设置为了主键，同时我们可以将多个字段都设置为主键，成为复合主键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [student]> create table student (
    ->     id int(5) default 1 comment '不能为空', 
    ->     name varchar(20) not null comment '姓名',
    ->     primary key(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | 1       |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

  这样name和id字段就形成了一个复合主键。同时如果我们插入主键完全相同的数据就会报错。

1
2
3
4
5
6
7
8
MariaDB [student]> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into student values(2,'张三');
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> insert into student values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

  我们也可以删除主键。

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [student]> alter table student drop primary key;
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   |     | 1       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  我们也可以在表建完后再添加主键。

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [student]> alter table student add primary key(id);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | 1       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  主键的操作十分重要，可以帮我们排序数据，查找数据等，是不可或缺的约束之一。

自增长

  当我们想要将我们插入的数据的序号自动增长时我们就可以使用这个约束。并且如果我们想要一个对一个字段使用自增长，这个字段前提必须是一个索引（key一栏有值），并且是整数类型。使用auto_increment添加自增长约束。同时添加了自增长的字段不能再设置默认值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    ->     id int primary key auto_increment comment '不能为空', 
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

  这样我就设置完毕了自增长，我们添加几个数据看看。

1
2
3
4
5
6
7
8
MariaDB [student]> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+
2 rows in set (0.00 sec)

  我们可以看我并不需我们再对id字段赋值，id字段便可以自动从1开始自动对数据进行编号同时我们可以查看目前自增长的数值。

1
2
3
4
5
6
7
8
9
10
11
MariaDB [student]> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '不能为空',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8     |
+---------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

  下面的AUTO_INCREMENT=3则表示了目前自增长数值到3了。

唯一键

  往往表中有多个数据需要唯一性，但是主键一个表中只能最多有一个，因此唯一键诞生了。唯一键可以很好的帮助表中各自段的数据确立唯一性，因此被唯一键约束的字段可以为空但并不能重复，其他特性都与主键类似。唯一键使用unique进行约束。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [student]> create table student (
    id int unique
    name varchar(20) unique
);
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  对字段进行唯一键约束后如果插入重复数据会如何呢？

1
2
3
4
5
MariaDB [student]> insert into student values(1, '张三');
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into student values(1, '张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

  很明显在这里报错了。

外键

  有时候我们的数据一张表往往是无法完全进行表示的，比如说对于一个学生表，我们要想将其与另外一张班级表进行连接，在他们间建立联系，就要依靠到外键这个约束。比如说接下来这个例子。

1
2
3
4
5
MariaDB [student]> create table class(
    ->     id int primary key,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

  首先我们创建了主表，我们将班级表作为主表，这个表要求必须要有主键或唯一键进行约束，也就是说各个数据要求是不重复的，不然从表与之建立联系时会产生矛盾。

1
2
3
4
5
6
7
MariaDB [student]> create table student (
    ->     id int primary key auto_increment,
    ->     name varchar(20),
    ->     class_id int comment '班级id',
    ->     foreign key (class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

  以上建立的这个表我们称之为从表，注意在最后我们加入了外键的语法，使之class_id字段与主表的id字段相互对应。接下来我们插入几个数据看看效果。

1
2
3
4
5
6
7
8
9
10
11
MariaDB [student]> insert into class value(1,'C++'), (2,'java');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [student]> insert into student values(1,'张三',1);
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> insert into student values(2,'李四',2);
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into student values(3,'老王',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`student`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

  从这个例子可以看出我们先给班级表插入了两个班级数据，在从表如果我们正常插入存在对应班级编号数据的学生数据时会正常插入，但是如果我们给老王插入了一个不存在班级id就会报错，同时如果我们给班级id赋值为空也是允许的。

-------------本文结束感谢您的阅读!-------------
记录学习每一分，感谢您的赞助