MySQL数据库（五）外键约束

外键约束（FOREIGN KEY）

学习导航：如果想了解其他四中约束，请看我上几篇博客

一、外键约束讲解

一、FOREIGN KEY 的作用和要求

• 作用：保证数据的一致性和完整性，实现一对一或者一对多的关系。（关系型数据库）

• 要求：

  a、父表和子表（具有外键列的表，子表所参照的表称之为父表）必须使用相同的存储引擎，而且禁止使用临时表。

  b、数据表的存储引擎只能为IbnnoDB。

  c、外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号必须相同；而字符的长度则不同。

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

---

二、如何编辑数据表的默认存储引擎

找到my.ini文件，打开，找到defult - stroage-engine = INNODB，查看引擎是否是innoDB,如下图：

---

三、案例验证

案例一、创建父类表和子类表，数据类型不一致导致150错误

mysql> create table sheng(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );

创建城市
mysql> create table city(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> cname varchar(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY(pid) references sheng(id));
ERROR 1005 (HY000): Can't create table 't1.city' (errno: 150)

案例二、创建父类表和子类表，符号不一致造成的错误

创建省份
mysql> create table sheng(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );

创建城市
mysql> create table city(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> cname varchar(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY(pid) references sheng(id));
ERROR 1005 (HY000): Can't create table 't1.city' (errno: 150)

案例三、正确的写法

创建省份
mysql> create table sheng(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );

创建城市
mysql> create table city(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> cname varchar(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) references sheng(id));
ERROR 1005 (HY000): Can't create table 't1.city' (errno: 150)

四、查看是否添加索引，注意，如果我们给数据表添加了主键，系统会自动添加索引

查看我们父类是否添加了索引

mysql> show indexes from sheng\G;
*************************** 1. row ***************************
        Table: sheng
   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)

ERROR:
No query specified

查看我们外键列city否添加了索引(们在验证依赖列city,我们在创建pid的时候并没有创建索引，查看下有没有创建)

mysql> show indexes from city\G;
*************************** 1. row ***************************
        Table: city
   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: city
   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