关于MySQL 外键索引

1、什么时候需要用到外键索引呢？

答：A表中存在唯一索引id_a，B表中的字段包含id_a，由于B表自身已经有主键索引，此时如果将字段id_a设置为外键索引索引（foreign key），则建立外键索引。

使用场景：

（1）父表更新时，子表也更新；父表删除时，如果

（2）子表有匹配的项，则删除失败。

（3）父表更新时，子表也更新；父表删除时子表匹配的项也删除。

即：使用外键可以使得修改或者删除的级联操作的日常维护工作更加轻松。

 

2、创建语法

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name(index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

（外键值最直接的作用）如果子表试图创建一个父表不存在的外键值，InnoDB会拒绝任何INSERT或者UPDATE操作。

如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。

InnoDB支持以下几种动作类型，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RETRICT:

（1）CASCADE：顾名思义：级联，从父表中删除或者更新对应的行，同时自动的删除或者更新子表中匹配的行。如：ON DELETE CANSCADE和ON UPDATE CANSCADE。（最有用）

（2）SET NULL：从父表中删除或更新对应的行，同时将子表中的外键列设置为空。注意，这些在外键列没有被设为NOT NULL时才生效。如：ON DELETE SET NULL和ON UPDATE SET NULL。

（3）NO ACTION：

（4）RESTRICT：拒绝删除或者更新父表。（默认）

举个栗子：

建立表province:

CREATE TABLE `province` (

  `id` smallint(6) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

建立表student:

CREATE TABLE `student` (

  `id` smallint(6) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) COLLATE utf8_bin NOT NULL,

  `pid` smallint(6) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `pik_fk` (`pid`),

  CONSTRAINT `pik_fk` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

)ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

上述student表中已经建立了外键级联索引（包括delete cascade 和 update cascade）

这里，先介绍下如何删除外键索引：可以看到建立外键索引产生了两个属性一个是字段pid被设置为索引，而且还加了一个约束属性。

删除的操作：

（1）dorp table student drop foreign key pik_fk;

（2）drop table student drop key pik_fk;

注意：需要分别执行两次pik_fk的操作。

执行上述操作之后，表结构如下：

CREATE TABLE `student` (

  `id` smallint(6) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) COLLATE utf8_bin NOT NULL,

  `pid` smallint(6) DEFAULT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin；

需求一：现在需要增加一个外键索引，当该外键对应的父表删除掉主键id，则对于的该表中关联的字段也要一起删除掉。增加如下：

alter table student add constraint pid_fk foreign key (pid) references province (id) on delete cascade on update cascade;

则添加外键索引：

CREATE TABLE `student` (

  `id` smallint(6) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) COLLATE utf8_bin NOT NULL,

  `pid` smallint(6) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `pid_fk` (`pid`),

  CONSTRAINT `pid_fk` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

)ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin；

此时，如果执行删除province中某条记录，则会删除student中所有有关联该条记录主键的记录。如果更新province中主键id，则会更新所有student中原有表对应的外键id。

需求二：如果删除各province中的主键id，则student中中外键pid将相应更改为null。

alter table student add constraint pid_fk foreign key (pid) references province (id) on delete set null on update set null;

需求三：如果province中的主键id在student中外键pid中存在，则不允许进行删除和更改操作。

alter table student add constraint pid_fk foreign key (pid) references province (id) on delete RESTRICT on update RESTRICT;

外键索引的缺点：

由于加了约束，因此操作变慢了。