mysql的外键举例子_mysql外键概述举例

MySQL手册关于外键的部分说明：

语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name,

...)

REFERENCES tbl_name

(index_col_name, ...)

[ON DELETE {RESTRICT |

CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT |

CASCADE | SET NULL | NO ACTION}]

外键定义服从下列情况：

·所有tables必须是InnoDB型,它们不能是临时表。

·在引用表中,必须有一个索引,外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在,它必须在

引用表里被自动创建。

·在引用表中,必须有一个索引,被引用的列以同样的顺序被列在其中作为第一列。

·不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中,这是因为对这些列的索引必须总是包含一个前缀长度。

·如果CONSTRAINTsymbol被给出,它在数据库里必须是唯一的。如果它没有被给出,InnoDB自动创建这个名字。

InnoDB拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的INSERT或UPDATE操作。

一个父表有一些匹配的行的子表，InnoDB对任何试图更新或删除该父表中候选键值的UPDATE或DELETE操作有所动作，这个动作取决于用FOREIGN

KEY子句的ON UPDATE和ON DETETE子句指定的referential action。

当用户试图从一个父表删除或更新一行之时，且在子表中有一个或多个匹配的行，InnoDB根据要采取的动作有五个选择:

·CASCADE:

从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE

CASCADE都可用。在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。

·SET NULL:

从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE

SET NULL和ON UPDATE SET NULL子句被支持。

·NO ACTION: 在ANSI SQL-92标准中,NO

ACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber,

掌握SQL, 2000:181)。 InnoDB拒绝对父表的删除或更新操作。

·RESTRICT: 拒绝对父表的删除或更新操作。NO

ACTION和RESTRICT都一样,删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查,并且NO

ACTION是一个延期检查。在MySQL中,外键约束是被立即检查的,所以NO ACTION和RESTRICT是同样的)。

·SET DEFAULT:

这个动作被解析程序识别,但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET

DEFAULT子句的表定义。

================================ 举例：用户--订单

================================

------测试表:用户表：

CREATE TABLE `users` (

`uid` int(11) NOT NULL AUTO_INCREMENT

COMMENT '用户id',

`name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',

PRIMARY KEY (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------测试表:订单表(使用的外键关联方式是RESTRICT，也就是不允许删除父表数据)：

CREATE TABLE `orders` (

`order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT

'订单编号',

`order_sn` char(8) NOT NULL DEFAULT '' COMMENT '订单号',

`uid` int(11) NOT NULL COMMENT '用户id',

PRIMARY KEY (`order_id`),

KEY `uid` (`uid`),

CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `users`

(`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入用户

mysql> insert into users(name)

-> values

('John');

Query OK, 1 row affected (0.00 sec)

插入订单

mysql> insert into orders(order_sn,uid)

values('CHKL4152',1);

Query OK, 1 row affected (0.00 sec)

查看

mysql> select * from users;

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

| uid | name |

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

|  1 | John |

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

1 row in set (0.00 sec)

mysql> select * from orders;

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

| order_id | order_sn | uid |

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

|  1 | CHKL4152 |  1

|

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

1 row in set (0.00 sec)

删除用户，报错了，提示不能删除或更新父表行！

mysql> delete from users where uid=1;

ERROR 1451 (23000): Cannot delete or update a parent row: a

foreign key constraint fails (`yao`.`orders`, CONSTRAINT `fk_uid`

FOREIGN KEY (`uid`) REFERENCES `users` (`uid`))

插入订单，报错了，提示不能增加或更新子表行(由于当前子表users中没有uid为2的用户)！

mysql> insert into orders(order_sn,uid)

values('CHKL4689',2);

ERROR 1452 (23000): Cannot add or update a child row: a

foreign key constraint fails (`yao`.`orders`, CONSTRAINT `fk_uid`

FOREIGN KEY (`uid`) REFERENCES `users` (`uid`))

修改外键触发限制(CASCADE，随父表删除/更新，子表删除/更新)

mysql> ALTER TABLE `orders` DROP FOREIGN KEY

`fk_uid`;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0

Warnings: 0

mysql> ALTER TABLE `orders` ADD CONSTRAINT

`fk_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE

CASCADE ON UPDATE CASCADE;

Query OK, 1 row affected (0.02 sec)

Records: 1  Duplicates: 0

Warnings: 0

再次删除用户表中uid为1的用户信息。

mysql> delete from users where uid=1;

Query OK, 1 row affected (0.00 sec)

查看,users和orders信息，和预想一样，没有了uid为1的相关信息。

mysql> select * from users;

Empty set (0.00 sec)

mysql> select * from orders;

Empty set (0.00 sec)

增加用户信息:

mysql> insert into users(name)

-> values

('John');

Query OK, 1 row affected (0.00 sec)

查看用户表

mysql> select * from users;

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

| uid | name |

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

|  2 | John |

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

1 row in set (0.00 sec)

增加订单信息：

mysql> insert into orders(order_sn,uid)

values('CHKL4689',2);

Query OK, 1 row affected (0.00 sec)

查看订单表：

mysql> select * from orders;

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

| order_id | order_sn | uid |

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

|  3 | CHKL4689 |  2

|

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

1 row in set (0.00 sec)

更新用户表uid，将uid为2的更新为5.

mysql> update users set uid=5 where

uid=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1

Warnings: 0

查看，和预想一样，uid为2的orders表中的信息也更新为5了。

mysql> select * from orders;

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

| order_id | order_sn | uid |

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

|  3 | CHKL4689 |  5

|

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

1 row in set (0.00 sec)

mysql>