mysql外鍵_MYSQL外鍵(Foreign Key)的使用

在MySQL 3.23.44版本后，InnoDB引擎类型的表支持了外键约束。

外键的使用条件：

1.两个表必须是InnoDB表，MyISAM表暂时不支持外键(据说以后的版本有可能支持，但至少目前不支持)；

2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；

3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

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

外键的定义语法：

[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(无动作，默认的)

搞个例子，简单演示一下使用，做dage和xiaodi两个表，大哥表是主键，小弟表是外键：

建表：

1

CREATETABLE`dage` (2

  `id`int(11)NOTNULLauto_increment,3

  `name`varchar(32)default'',4

PRIMARYKEY(`id`)5

) ENGINE=InnoDBDEFAULTCHARSET=latin1；6

7

CREATETABLE`xiaodi` (8

  `id`int(11)NOTNULLauto_increment,9

  `dage_id`int(11)defaultNULL,10

  `name`varchar(32)default'',11

PRIMARYKEY(`id`),12

KEY`dage_id` (`dage_id`),13

CONSTRAINT`xiaodi_ibfk_1`FOREIGNKEY(`dage_id`)REFERENCES`dage` (`id`)14

) ENGINE=InnoDBDEFAULTCHARSET=latin1；

插入个大哥：

1

mysql>insertintodage(name)values('铜锣湾');2

Query OK,1row affected (0.01sec)3

mysql>select*fromdage;4

+----+--------+5

|id|name|6

+----+--------+7

|1|铜锣湾|8

+----+--------+9

1rowinset(0.00sec)

插入个小弟：

1

mysql>insertintoxiaodi(dage_id,name)values(1,'铜锣湾_小弟A');2

Query OK,1row affected (0.02sec)3

4

mysql>select*fromxiaodi;5

+----+---------+--------------+6

|id|dage_id|name|7

+----+---------+--------------+8

|1|1|铜锣湾_小弟A|9

+----+---------+--------------+

把大哥删除：

1

mysql>deletefromdagewhereid=1;2

ERROR1451(23000): Cannotdeleteorupdatea parent row: aforeignkeyconstraintfails (`bstar/xiaodi`,CONSTRAINT`xiaodi_ibfk_1`FOREIGNKEY(`dage_id`)REFERENCES`dage` (`id`))

提示：不行呀，有约束的，大哥下面还有小弟，可不能扔下我们不管呀！

插入一个新的小弟：

1

mysql>insertintoxiaodi(dage_id,name)values(2,'旺角_小弟A');2

ERROR1452(23000): Cannotaddorupdatea child row: aforeignkeyconstraintfails (`bstar/xiaodi`,CONSTRAINT`xiaodi_ibfk_1`FOREIGNKEY(`dage_id`)REFERENCES`dage` (`id`))3

提示：小子，想造反呀！你还没大哥呢！

把外键约束增加事件触发限制：

1

mysql>showcreatetablexiaodi;2

3

CONSTRAINT`xiaodi_ibfk_1`FOREIGNKEY(`dage_id`)REFERENCES`dage` (`id`)4

5

mysql>altertablexiaodidropforeignkeyxiaodi_ibfk_1;6

Query OK,1row affected (0.04sec)7

Records:1Duplicates:0Warnings:8

mysql>altertablexiaodiaddforeignkey(dage_id)referencesdage(id)ondeletecascadeonupdatecascade;9

Query OK,1row affected (0.04sec)10

Records:1Duplicates:0Warnings:0

再次试着把大哥删了：

1

mysql>deletefromdagewhereid=1;2

Query OK,1row affected (0.01sec)3

4

mysql>select*fromdage;5

Emptyset(0.01sec)6

7

mysql>select*fromxiaodi;8

Emptyset(0.00sec)

得，这回对应的小弟也没了，没办法，谁让你跟我on delete cascade了呢！

例子说明的应该蛮清楚了吧，其他功能对应手册自己实践吧！:-)