MYSQL 字段约束之外键约束的要求2020-11-18

MYSQL 字段约束之外键约束的要求
1，父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2，数据表的存储引擎只能是InnoDB;
3，外键列和参考列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符长度则可以不同；
4，外键列和参照列必须创建索引。如果外键列不存在索引的话，MYSQL将自动创建索引。
5，查看索引：SHOW INDEXES FROM tab_name\G;

 corse | CREATE TABLE `corse` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` char(10) NOT NULL,
 PRIMARY KEY (`id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8 |


 CREATE TABLE ers(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username CHAR(10) NOT NULL,
 cid INT UNSIGNED,
 FOREIGN KEY (cid) REFERENCES corse (id)
 );


| ERS   | CREATE TABLE `ers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` char(10) NOT NULL,
  `cid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  CONSTRAINT `ers_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `corse` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |



mysql> SHOw INDEXes FROM ers\G;
*************************** 1. row ***************************
        Table: ers
   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: ers
   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:
2 rows in set (0.00 sec)



mysql> SHOw INDEXes FROM corse\G;
*************************** 1. row ***************************
        Table: corse
   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)