MySQL建表中的约束

除增删改查外，我们在业务中还会涉及到MySQL建表过程。建表语句中，约束性条件是一个比较杂乱的知识点。
对于某些列来说，可能有一些特殊含义或者语法，我们把这些特殊的含义或用法称为列的属性，也可以称为列的约束性条件，在创建表的时候可以显式的定义出来。

默认值

如果在指定的插入列中省略了某些列，那这些列的值将被设置为NULL，也就是列的默认值为NULL，表示没有设置值。我们在创建表的时候也可以指定一些有意义的默认值，指定方式如下：
比如我们把first_table的second_column列的默认值指定为'abc'，创建一下这个表：

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>

插入一条语句后

mysql> INSERT INTO first_table(first_column) VALUES(1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | abc           |
+--------------+---------------+
1 row in set (0.00 sec)
mysql>

我们的插入语句并没有指定second_column的值，但是可以看到插入结果是按照我们规定的默认值'abc'来设置的。如果我们不设置默认值，其实就相当于指定的默认值为NULL，比如first_table表并没有设置first_column列的默认值，那它的默认值就是NULL，也就是说上边的表定义语句和下边这个是等价的：

CREATE TABLE first_table (
    first_column INT DEFAULT NULL,
    second_column VARCHAR(100) DEFAULT 'abc'
);

非空约束

对于某些列，我们要求它们是必填的，也就是不允许存放NULL值，我们用这样的语法来定义这个列：
比如我们把first_table的first_column列定义为NOT NULL。当然，我们在重新定义表之前需要把原来的表删掉：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE first_table (
    ->     first_column INT NOT NULL,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>

这样的话，我们就不能再往这个字段里插入NULL值了，比如这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, 'aaa');
ERROR 1048 (23000): Column 'first_column' cannot be null
mysql>

可以看到，弹出了错误提示。
一旦对某个列定义了NOT NULL属性，那这个列的默认值就不为NULL了。上边first_column并没有指定默认值，意味着我们在使用INSERT插入行时必须显式的指定这个列的值，而不能省略它，比如这样就会报错的：

mysql> INSERT INTO first_table(second_column) VALUES('aaa');
ERROR 1364 (HY000): Field 'first_column' doesn't have a default value
mysql>

可以看到执行结果提示我们first_column并没有设置默认值，所以在插入数据的时候不能省略掉这个列的值。

主键

有时候在我们的表里可以通过某个列或者某些列确定唯一的一条记录，我们就可以把这个列或者这些列称为候选键。比如在学生信息表student_info中，只要我们知道某个学生的学号，就可以确定一个唯一的学生信息，也就是一条记录。当然，我们也可以通过身份证号来确定唯一的一条学生信息记录，所以学号和身份证号都可以作为学生信息表的候选键。在学生成绩表student_score中，我们可以通过学号和科目这两个列的组合来确定唯一的一条成绩记录，所以学号、科目这两个列的组合可以作为学生成绩表的候选键。
一个表可能有多个候选键，我们可以选择一个候选键作为表的主键。从定义中就可以看出，一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的一条记录。如果我们的表中有定义主键的需求可以选用下边这两种方式之一来指定主键：

1. 如果主键只是单个列的话，可以直接在该列后声明PRIMARY KEY，比如我们把学生信息表student_info的学号列声明为主键可以这么写：

   CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
   );

2. 我们也可以把主键的声明单独提取出来，用这样的形式声明：

   PRIMARY KEY (列名1, 列名2, ...)

   然后把这个主键声明放到列定义的后边就好了。比如student_info的学号列声明为主键也可以这么写：

   CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    PRIMARY KEY (number)
   );

   值得注意的是，对于多个列的组合作为主键的情况，必须使用这种单独声明的形式，比如student_score表里的学号,科目的列组合作为主键，可以这么声明：

   CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
   );

   在我们创建表的时候就声明了主键的话，MySQL会对我们插入的记录做校验，如果两条记录里有相同的主键值的话就会报错。
   另外，主键列默认是NOT NULL的，也就是必填的，如果填入NULL值会报错(先删除原来的student_info表，使用上边的两种方式之一重新创建表之后再执行下边的语句)：

   mysql> INSERT INTO student_info(number) VALUES(NULL);
   ERROR 1048 (23000): Column 'number' cannot be null
   mysql>

   所以大家在插入数据的时候至少别忘了给主键列赋值哈～

   唯一性约束

   对于不是主键的其他候选键，如果我们也想让MySQL替我们校验数据的唯一性，那我们可以把这个列或列组合声明为UNIQUE的，表明该列或者列组合的值是不允许重复的，这种列的属性叫做唯一性约束。同主键的定义一样，唯一性约束的定义也有两种方式：

3. 如果具有唯一性约束的列是单个列的话，可以直接在该列后声明UNIQUE或者UNIQUE KEY，比如在学生信息表student_info中，我们不允许两条学生记录中的身份证号是一样的，那我们让id_number这一列具有唯一性约束。

   CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18) UNIQUE,
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
   );

4. 我们也可以把唯一性约束的声明单独提取出来，用这样的形式声明：

   UNIQUE [约束名称] (列名1, 列名2, ...)

   或者：

   UNIQUE KEY [约束名称] (列名1, 列名2, ...)

   其中的约束名称是可选的，其实就是我们为这个唯一性约束起的一个名字而已，如果不起名字的话该名称默认和列名相同，这个不重要哈～ 然后把这个唯一性约束声明放到列定义的后边就好了。比如student_info的身份证号列声明唯一性约束的属性也可以这么写：

   CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY (id_number)
   );

   值得注意的是，对于多个列的组合具有唯一性约束的情况，必须使用这种单独声明的形式。
   如果表中定义了唯一性约束的话，MySQL会对我们插入的记录做校验，如果插入的值违反了唯一性约束的话就会报错！

   主键和唯一性约束的区别

   主键和唯一性约束都能保证某个列或者列组合的唯一性，但是：

5. 一张表中只能定义一个主键，却可以定义多个唯一性约束！
   

6. 主键列不允许存放NULL值，而普通的唯一性约束列可以存放NULL值！

   小贴士：
   你可能会问为啥主键列不允许存放NULL值，而普通的唯一性约束列却可以呢？哈哈，这涉及到底层存储的事情，现在你只需要记住这个规定就好了，如果你想知道更多的事情，那就继续往后看呗～

   外键

   插入到学生成绩表student_score中的学号(number)列中的值必须能在学生基本信息表student_info中的学号列中找到，否则如果一个学号只在成绩表里出现，而在信息表里找不到相应的记录的话，就相当于插入了一个不知道是哪个学生的成绩，这显然是荒谬的。为了防止这样荒谬的情况出现，MySQL给我们提供了外键约束机制。定义外键的语法是这样的：

   CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

   其中的外键名称也是可选的，一个名字而已，有没有都行，不是很重要～ 如果A表中的某个列或者某些列依赖与B表中的某个列或者某些列，那么就称A表为子表，B表为父表。子表和父表可以使用外键来关联起来，上边例子中student_info就是一个父表，student_score就是子表，我们可以这样来定义student_score列，来使用外键关联起父表和子表：

   CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
   );

   这样，在对student_score表插入数据的时候，MySQL都会为我们检查一下插入的学号是否能在student_info中找到，如果找不到则会报错。

   父表中作为外键的列或者列组合必须建立索引，主键和具有唯一性约束的列默认的都建立了索引，置于什么是索引，我们之后会详细唠叨的。

   自增

   如果一个表中的某个列是数值类型的，包括整数类型和浮点数类型，那么这个列可以设置自增属性。所谓自增，意思是如果我们在插入数据的时候不指定该列的值，那么该列的值就是上一列的值加1后的值，定义语法就是这样：

   列名 列的类型 AUTO_INCREMENT

   比如我们想在first_table里设置一个自增列id，并把这个列设置为主键，来唯一标记一行记录，我们可以这么写：

   mysql> DROP TABLE first_table;
   Query OK, 0 rows affected (0.00 sec)
   mysql> CREATE TABLE first_table (
    ->     id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
   Query OK, 0 rows affected (0.01 sec)
   mysql>

   先把原来的表删掉，然后在新表中增加了一个名为id、数据类型为INT UNSIGNED类型的列，并把它设置为主键而且具有递增属性，那我们插入数据的时候就可以不用管这个列，但是它的值将会递增，看：

   mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa'), (2, 'bbb'), (3, 'ccc');
   Query OK, 3 rows affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   mysql> SELECT * FROM first_table;
   +----+--------------+---------------+
   | id | first_column | second_column |
   +----+--------------+---------------+
   |  1 |            1 | aaa           |
   |  2 |            2 | bbb           |
   |  3 |            3 | ccc           |
   +----+--------------+---------------+
   3 rows in set (0.00 sec)
   mysql>

   可以看到，列id是从1开始递增的。在使用递增属性的时候需要注意这几点：

7. 一个表中最多有一个递增列。
   

8. 一般只为整数类型的列定义递增属性，浮点数类型基本不用递增属性。
   
9. 具有AUTO_INCREMENT属性的列必须建立索引。主键和具有唯一性约束的列会自动建立索引，至于什么是索引，我们后边会详细唠叨。
   
10. 一般递增列都是作为主键的属性，来自动生成唯一标识一个记录的主键值。
    
11. 因为具有AUTO_INCREMENT属性的列是从1开始递增的，所以最好用UNSIGNED来修饰这个列，可以提升正数的表示范围。
    

    约束性条件的组合

    每个列可以有多个约束性条件，声明的顺序无所谓，各个约束性条件之间用空白隔开就好了～

    注意，有的约束性条件是冲突的，一个列不能具有两个冲突的约束性条件，比如一个列不能既声明为`PRIMARY KEY`，又声明为`UNIQUE KEY`，不能既声明为`DEFAULT NULL`，又声明为`NOT NULL`。大家在使用过程中需要注意这一点。