【一步一步学习mysql】约束

• 约束保证数据的完整性和一致性
• 约束分为表级约束和列级约束
• 约束类型包括：
  • NOT NULL（非空约束）
  • PRIMARY KEY（主键约束）
  • UNIQUE KEY（唯一约束）
  • DEFAULT（默认约束）
  • FOREIGN KEY（外键约束）

非空约束

NULL，可以为空
NOT NULL，不可以为空

mysql> CREATE TABLE user2( name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.28 sec)
mysql> SHOW COLUMNS FROM user2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键约束

PRIMARY KEY

• 主键约束
• 每张数据表只能存在一个主键
• 主键保证记录的唯一性
• 主键自动为NOT NULL

mysql> CREATE TABLE user3( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.23 sec)

mysql> SHOW COLUMNS FROM user3;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned  | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT user3(name, age) VALUES('xxx', 30);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT user3(name, age) VALUES('yyy', 33);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM user3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xxx  |   30 |
|  2 | yyy  |   33 |
+----+------+------+
2 rows in set (0.01 sec)

唯一约束

UNIQUE KEY

• 唯一约束
• 唯一约束可以保证记录的唯一性
• 唯一约束的字段可以为空值(NULL)
• 每张数据表可以存在多个唯一约束

mysql> CREATE TABLE user4( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE KEY, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW COLUMNS FROM user4;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   | UNI | NULL    |                |
| age   | tinyint(3) unsigned  | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

默认约束

DEFAULT

• 默认值
• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

mysql> CREATE TABLE user5( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE KEY, sex ENUM('F', 'M', 'NA') DEFAULT 'M');
Query OK, 0 rows affected (0.31 sec)

mysql> SHOW COLUMNS FROM user5;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   | UNI | NULL    |                |
| sex   | enum('F','M','NA')   | YES  |     | M       |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

外键约束

保持数据一致性，完整性。实现一对一或一对多关系。我觉得这就是为啥叫做关系型数据库的原因。

• 父表和子表必须使用相同的存储引擎（InnoDB），而且禁止使用临时表。
• 外键列和参照列必须具有相似的数据类型。要我说就用相同的即可。
• 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL会自动创建索引。

FOREIGN KEY(外键列) REFERENCES 父表(参照列)

# 父表
mysql> CREATE TABLE province( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.27 sec)

# 子表
mysql> CREATE TABLE users(id SMALLINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES province(id));
Query OK, 0 rows affected (0.24 sec)

# 查看子表的索引，可以看到外键列被自动加上了索引
mysql> SHOW INDEXES FROM usersG;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

外键约束的参照操作

• CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
• SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL，前提是该外键列没有设置NOT NULL。
• RESTRICT：拒绝对父表的删除或更新操作。

# 创建一个使用参照的表 users2 【ON DELETE SET NULL ON UPDATE CASCADE】
mysql> CREATE TABLE users2(id SMALLINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES province(id) ON DELETE SET NULL ON UPDATE CASCADE);
Query OK, 0 rows affected (0.30 sec)

# 向父表和子表中进行数据填充
mysql> insert province(name) values('山东省');
mysql> insert province(name) values('江苏省');
mysql> insert province(name) values('浙江省');

mysql> insert users2(name, pid) values('a', 1);
mysql> insert users2(name, pid) values('b', 2);
mysql> insert users2(name, pid) values('c', 1);

# 更改父表中的数据，子表中的数据做出相应的改变
mysql> update province set id = 5 where id = 1;
mysql> select * from users2;
+----+------+------+
| id | name | pid  |
+----+------+------+
|  1 | a    |    5 |
|  2 | b    |    2 |
|  3 | c    |    5 |
+----+------+------+
3 rows in set (0.00 sec)

# 删除父表中的数据，子表中的数据置NULL
mysql> delete province from id = 2;
mysql> select * from users2;
+----+------+------+
| id | name | pid  |
+----+------+------+
|  1 | a    |    5 |
|  2 | b    | NULL |
|  3 | c    |    5 |
+----+------+------+
3 rows in set (0.00 sec)

注意：由于外键操作仅支持InnoDB，所以应用之前要考虑清楚。

列级约束和表级约束

• 对一个数据列建立的约束，称为列级约束。
• 对多个数据列建立的约束，称为表级约束。
• 列级约束既可以在列定义时声明，也可以在列定义后声明。
• 表级约束只能在列定义时约束。

一般都是在应用列级约束即可，不用太关注。