MySQL（四）

---

表的约束

---

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性。表的约束有很多，本篇只介绍如下：null/not null, default, comment, zerofill, primary key, auto_increment, unique key.
1. 空属性
空属性包括null(默认)和 not null(非空)。数据库默认字段基本上都是字段为空，但是在实际应用中，要尽可能保证字段不为空，因为数据为空没有办法参与运算。如：

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+null;
+--------+
| 1+null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

2. 默认值
某种数据会经常性地出现某个具体的值，可以在一开始就指定好，在需要真实数据时，用户可以选择性地使用默认值。但是默认值生效的前提是，数据在插入时不给该字段赋值。如：

mysql> create table t(
    -> name varchar(32) not null,
    -> age tinyint unsigned default 0,
    -> sex char(2) default '男'
    -> );

mysql> desc t;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(32)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t(name) values ('zhangsan');

mysql> select * from t;
+----------+------+------+
| name     | age  | sex  |
+----------+------+------+
| zhangsan |    0 | 男   |
+----------+------+------+
1 row in set (0.00 sec)

3. 列描述
comment 没有实际的含义，专门用来描述字段，会根据表创建语句保存。
以上述表 t 为例：通过desc看不到注释信息，但是通过show可以看到：

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `name` varchar(32) NOT NULL,
  `age` tinyint(3) unsigned DEFAULT '0',
  `sex` char(2) DEFAULT '男'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. 主键
primary key 用来唯一约束该字段里面的数据，不能重复且不能为空，一张表最多只能有一个主键，主键所在的列通常都是整数类型。
如：创建表的时候直接就要指定主键

mysql> create table tt(
    -> id int unsigned primary key comment '学号不能为空',
    -> name varchar(32) not null);

mysql> desc tt;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(32)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

在创建表时，在所有字段之后，使用 primary key 来创建主键，若有多个字段作为主键，可以使用复合主键。

mysql> create table t1(
    -> id int unsigned,
    -> course char(10),
    -> score tinyint unsigned default 60,
    -> primary key(id, course)
    -> );

mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| course | char(10)            | NO   | PRI | NULL    |       |
| score  | tinyint(3) unsigned | YES  |     | 60      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

当表创建好了之后可以再次追加主键

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

主键约束：主键对应的字段中不能有重复，一旦重复，操作失败。如：

mysql> insert into t values (1, 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (1, 'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当然会追加主键，也要会删除主键，删除主键如下：
删除前后对比：

mysql> desc t;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

执行删除主键操作之后：

mysql> alter table t drop primary key;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5. 自增长
auto_increment: 当对应的字段不给值，会自动被系统触发，系统会在当前字段中已经有的最大值进行 +1 操作，得到一个新的不同的值，通常与主键搭配使用，作为逻辑主键。
特点：
（1）任何一个字段要做自增长，前提是本身是一个索引（是否是索引，从key栏可以看到）；
（2）自增长字段必须是整数；
（3）一张表最多有一个自增长。
如：

mysql> create table tt1(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tt1(name) values('a');

mysql> insert into tt1(name) values('b');

mysql> select * from tt1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

6. 唯一键
unique: 一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，这才引入了唯一键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键和主键本质上差不多，但是唯一键允许为空，而且可以多个为空，只是空字段不做唯一性比较。如下：

mysql> create table stu(
    -> id char(10) unique,
    -> name varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into stu(id, name) values ('01', 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(id, name) values ('01', 'bbb');
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into stu(id, name) values (null, 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
| 01   | aaa  |
| NULL | bbb  |
+------+------+

从上述例子就可以看出，唯一约束不能重复，但是可以为空。
7. 外键
foreign key: 外键用于定义主从表之间的关系，主要定义在从表上，主表则必须有主键约束或唯一键约束。当定义外键后，要求外键列数据必须在主表的列存在或为null。如下：创建一个 student 表和一个 myclass 表，通过 student->class_id和myclass_id 形成关联的关系。
创建主表：

mysql> create table myclass(
    -> id int primary key,
    -> name varchar(32) not null);
Query OK, 0 rows affected (0.03 sec)

创建从表：

mysql> create table student(
    -> id int primary key,
    -> name varchar(32) not null,
    -> class_id int,
    -> foreign key(class_id) references myclass(id));
Query OK, 0 rows affected (0.04 sec)

正常插入数据：

mysql> insert into myclass values(10, 'C++'),(20, 'java');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into student values(100, 'zhang', 10),(101, 'li', 20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

插入班级号为30的学生，失败（班级不存在），如下：

mysql> insert into student values(102, 'wang', 30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`wo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

插入班级id 为null 的学生，新生目前没有分配班级时：

mysql> insert into student values(102, 'wang',null);
Query OK, 1 row affected (0.00 sec)