外键约束(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