3-2：MySQL 外键约束的要求解析

一.  约束意义和分类

约束保证数据的完整性和一致性。

约束分为表级约束和列级约束。

二.  约束类型：

约束类型包括:

• NOT NULL(非空约束)
• PRIMARY KEY(主键约束)
• UNIQUE KEY(唯一约束)
• DEFAULT(默认约束)
• FOREIGN KEY(外键约束)

三.  FOREIGN KEY(外键约束)

外键约束是为了保持数据一致性和完整性

实现一对一或者一对多的关系

四.  外键约束的要求

• 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
• 数据表的存储引擎只能为InnoDB
• 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同。
• 外键列和参照列必须创建索引|。如果外键列不存在索引的话，MySQL将自动创建索引。

五.  编辑数据表的默认存储引擎

打开my.ini文件，将default-storage-engine=设置为INNODB（一般默认就是INNODB），如下：

default-storage-engine=INNODB

六.  外键和参照列的数据类型

首先创建一个父表：省份数据表：

将id字段设置主键约束（PRIMARY KEY）和自动编号（AUTO_INCREMENT）

pname字段为省份名字

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );

查看该表的存储引擎，可以用SHOW CREATE TABLE命令显示用于创建给定表的CREATE TABLE语句：

mysql> SHOW CREATE TABLE provinces;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

可以看到存储引擎为InnoDB

接来下再创建一个子表：用户表：

pid是用户的省份，这里不需要单独再加一个字符型的省份字段，因为是关系型数据库，这里只需要存储省份的编号即可

pid也就是外键列，将参照主键表也就是父表中的id字段，id为参照列

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

可以看到，我们将pid设置为BIGINT，因为外键列和参照列必须具有相似的数据类型，所以这里会报错。

接下来我们将pid设置为id类型设置为一致类型：

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

发现依旧报错，这是为什么呢？其实是因为在父表中，id的数据类型不光是SMALLINT，还是UNSIGNED，所以pid也必须为无符位才可以

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

创建成功！接下来看一下外间列和参照列是否创建了索引呢？

首先查看参照列id，我们并没为参照列创建索引，那么参照列是否自动创建了索引呢？答案是肯定的，因为id是主键，而在MySQL中，当你建立主键时，索引同时也已建立起来了，我们通过

show index from table_name;

命令来显示表的索引：

mysql> SHOW INDEXES FROM provinces;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| provinces |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

在SHOW命令后添加\G（这里值得注意的是，教程中老师习惯性的在\G后面加上;这样会导致No query specified错误，其实\G后面不需要加;）便于查看：

mysql> SHOW INDEXES FROM provinces \G
*************************** 1. row ***************************
        Table: provinces
   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:
1 row in set (0.00 sec)

可以看到id字段已经创建了索引

再来看一下外间列：

mysql> SHOW INDEXES FROM users \G;
*************************** 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)

ERROR:
No query specified

可以看到users表中，一共有两个索引：主键索引和pid索引（pid索引是系统自动创建）

再查看一下users表：

mysql> SHOW CREATE TABLE users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到FOREIGN KEY (`pid`)说明pid为外键