MySQL外键(FOREIGN KEY)的简单使用

基本概念

1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。
3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

外键的使用条件

• ① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
• ② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
• ③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

使用方法

1、创建外键的语法：

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
    REFERENCES tbl_name (index_col_name, …)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。如果指定，它在数据库里必须是唯一的。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

• ① RESTRICT（限制外表中的外键改动，默认值，最安全的设置）
• ② CASCADE（跟随外键改动）
• ③ SET NULL（设空值）
• ④ SET DEFAULT（设默认值）
• ⑤ NO ACTION（无动作，默认的）

//FK_ID是外键的名称
/*
CREATE TABLE `tb_active` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/

2、示例

1）创建表

表1：

CREATE TABLE `category_table`(
 `category_id` varchar(6) not null primary key,
 `category_name` varchar(32) not null
 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

表2：

CREATE TABLE `goods_table`(
 goods_id varchar(20) not null primary key,
 goods_name char(13) not null,
 category_id char(13) not null,
 constraint `category_id_check` foreign key(`category_id`) references `category_table`(`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

2）插入数据

insert into `category_table` values("11", "数码"); //success
insert into `category_table` values("12", "衣服"); //success
insert into `goods_table` values("1001", "手机", "11"); //success
insert into `goods_table` values("1002", "衬衫", "12"); //success
insert into `goods_table` values("1003", "西瓜", "13"); //failed,提示：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ouloon_test`.`goods_table`, CONSTRAINT `category_id_check` FOREIGN KEY (`category_id`) REFERENCES `category_table` (`category_id`))

3）增加级联操作

mysql> alter table goods_table
    -> add constraint category_id_check
    -> foreign key(category_id)
    -> references category_table(category_id)
    -> on delete cascade
    -> on update cascade;

3、外键相关语法

1）为已经添加好的数据表添加外键:

语法：alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
示例：

alter table `tb_active` add constraint `FK_ID` foreign key(`user_id`) REFERENCES `tb_user`(`id`)

2）删除外键

语法：alter table table-name drop foreign key key-id;
示例：

alter table `tb_active` drop foreign key `FK_ID`

3）查看表有哪些外键

语法：show create table table-name

4）在外键上建立索引:

index `category_id`(`category_id`),
foreign key(`category_id`) references `category_table`(`category_id`)

外键可以保证新插入的记录的完整性，但是在通过ON UPDATE 和 ON DELETE规则，设置MYSQL实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏。