Mysql 外键约束的要求

• 首先需要知道存储引擎是否为InnoDB。打开Mysql文件夹下的my.ini，找到 default-storage-engine=INNODB

• 创建一个表provinces，查看引擎是否为InnoDB。

mysql> USE text;
Database changed
mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (2.91 sec)

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 |     //确认引擎为InnoDB
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

• 其次，看外键列与参照列是否有相同的数据类型。若类型不同，会怎样？

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 1215 (HY000): Cannot add foreign key constraint    //此处外键列类型为BIGINT,参照列为SMALLINT,报错

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 1215 (HY000): Cannot add foreign key constraint  //此处外键列为有符号，参照列为无符号，报错

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.87 sec)        //创建成功

• 最后，看外键列与参照列是否有索引

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 | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| provinces |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (2.42 sec)

mysql> SHOW INDEXES FROM provinces\G;    
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id      //id存在索引
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SHOW INDEXES FROM users\G;     
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id      //id存在索引
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid    //pid存在索引
    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 (2.56 sec)

ERROR:
No query specified       

• 查看表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)