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