MySQL约束三

唯一约束UNIQUE KEY

• 唯一约束可以保证数据的唯一性

• 唯一约束的字段可以为空值

• 每张数据表可以存在多个唯一约束 下面创建一个表KEY里面tel和username是UNI，代表电话和用户名是唯一的。

mysql> CREATE TABLE tb6(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> username CHAR(10) NOT NULL UNIQUE KEY,
    -> tel CHAR(11) NOT NULL UNIQUE KEY,
    -> sex ENUM('男','女','保密') NOT NULL DEFAULT '男');
Query OK, 0 rows affected (0.04 sec)


mysql> DESC tb6;
+----------+------------------------+------+-----+---------+----------------+
| Field    | Type                   | Null | Key | Default | Extra          |
+----------+------------------------+------+-----+---------+----------------+
| id       | int                    | NO   | PRI | NULL    | auto_increment |
| username | char(10)               | NO   | UNI | NULL    |                |
| tel      | char(11)               | NO   | UNI | NULL    |                |
| sex      | enum('男','女','保密') | NO   |     | 男      |                |
+----------+------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

我们来插入一行与上一条一样的数据，可以看到，xiaer这个用户不是唯一的，所以报错了。接下来修改一下username和tel，可以插入成功，查询发现，id是3。这是因为之前插入了一条失败的数据，id是2被内条失败的语句抢走了。

mysql> INSERT INTO tb6() VALUES(null,'xiaoer','13538888886','男');
ERROR 1062 (23000): Duplicate entry 'xiaoer' for key 'tb6.username'
mysql> INSERT INTO tb6() VALUES(null,'xiaoer1','13538888885','男');
Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM tb6;
+----+----------+-------------+-----+
| id | username | tel         | sex |
+----+----------+-------------+-----+
|  1 | xiaoer   | 13538888886 | 女  |
|  3 | xiaoer1  | 13538888885 | 男  |
+----+----------+-------------+-----+
2 rows in set (0.00 sec)

外键约束FOREIGN KEY

• 保持数据的唯一性，完整性

• 实现一对一或一对多关系

要求

• 父表和子表必须使用相同的储存引擎，而且禁止使用临时表

• 数据表的储存引擎只能为InnDB

• 外键列和参照列必须具有相似的数据类型。其中数字长度或是符号位必须相同，而字符的长度可以不同

• 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引

• 查看索引：SHOW INDEXE FROM 表名 首先创建一个父表，然后查看,看到引擎位InnoDB。

mysql> CREATE TABLE tb7(
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> user CHAR(10) NOT NULL);
Query OK, 0 rows affected (0.04 sec)


mysql> SHOW CREATE TABLE tb7;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb7   | CREATE TABLE `tb7` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

接下来创建一个子表,注意，外键列需要和参照列类型必须一致，如果不一致则出现以下问题,因为参照列没有设置无符号，所以才报错。

mysql> CREATE TABLE tb8(
    -> user CHAR(10) NOT NULL,
    -> cid INT,
    -> FOREIGN KEY (cid) REFERENCES tb7(id)
    -> );
ERROR 3780 (HY000): Referencing column 'cid' and referenced column 'id' in 
foreign key constraint 'tb8_ibfk_1' are incompatible.

查看外键列索引,它有两行，第一行是建表时就产生的，第二行是cid也创建了索引。

mysql> SHOW INDEXES FROM tb8\G;
*************************** 1. row ***************************
        Table: tb8
   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:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: tb8
   Non_unique: 1
     Key_name: cid
 Seq_in_index: 1
  Column_name: cid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

外键约束参照操作

• CASCADE ON TPDATE/DELETE CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行

• SET NULL:从父表删除或更新行，并设置子表的外键列位NULL，此要求必须要保证子表没有指定NOT NULL

• RESTRICT:拒绝对父表更新或删除操作

• NO ACTION:标准SQL关键字，在MYSQL中与RESTRICT相同