完整性约束条件：唯一性约束

唯一性约束UNIQUE

1. 被标注柽UNIQUE的字段，插入的值不能够重复
2. NULL值很特殊，他不算重复值
3. 这里我们还是以创建会员表为例

mysql> CREATE TABLE IF NOT EXISTS user9(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> card CHAR(18) UNIQUE
    -> );
Query OK, 0 rows affected (0.80 sec)

mysql> INSERT user9(username) VALUES("zhouruc16");
Query OK, 1 row affected (0.48 sec)

mysql> INSERT user9(username, card) VALUES("rzhou8","123");
Query OK, 1 row affected (0.38 sec)

mysql> INSERT user9(username, card) VALUES("rzhou9",NULL);
Query OK, 1 row affected (0.12 sec)

mysql> DESC user9;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   | UNI | NULL    |                |
| card     | char(18)         | YES  | UNI | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE user9;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user9 | CREATE TABLE `user9` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT*FROM user9;
+----+-----------+------+
| id | username  | card |
+----+-----------+------+
|  1 | zhouruc16 | NULL |
|  2 | rzhou8    | 123  |
|  3 | rzhou9    | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)

总结完整性约束：

1. UNIQUE唯一性
   – 输入的值不能重复

2. NOT NULL 非零
   – 不能输入空值，必须要用内容，经常与默认值一起使用

3. DEFAULT 默认值
   – 给予一个默认值，当不插入内容的时候将会自动填入默认值

4. KEY 主键
   – 主键是唯一标识，一个表格只能有一个主键，可以存在复合主键，但是复合主键也算是一个主键

5. AUTO_INCREMENT 自增长
   – 按顺序使插入的内容依次递增，被标注的自增长字段只能是主键，并且一个表只能有一个自增长值

6. FORNIGN KEY 外键（之后会讲到）