可通过查表信息查看外键的创建信息:Show create table tablename \G;
从mysql 3.23.44开始,innodb支持外键约束,跟Oracle基本相同,语法形式如下
[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}]
ALTER TABLE yourtablename
ADD [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}]
外键约束对子表的含义:
如果在父表中找不到该键的值,则不允许在子表上进行insert/update。
外键约束对父表的含义:
在父表上进行update/delete以更新或删除,在子表中有一条或多条对应匹配行的外键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式, 分列如下
事件触发限制:
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子句的表定义。
在已存在的表中创建外键
alter table 外键所在的表名 add constraint 外键名 foreign key(外键所在的列名) references 对应主键所在表名(主键所在列名);
alter table 外键所在的表名 add constraint 外键名 foreign key(外键所在的列名) references 对应主键所在表名(主键所在列名) ON DELETE CASCADE ON UPDATE NO ACTION;
删除外键
alter table tablename drop foreign key namefk;
外键的作用:
保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值!
例如:
a b 两个表
a表中存有 客户号,客户名称
b表中存有 每个客户的订单
有了外键后
你只能在确信b 表中没有客户x的订单后,才可以在a表中删除客户x
建立外键的前提: 本表的列必须与外键类型相同(外键必须是外表主键)。
指定主键关键字: foreign key(列名)
引用外键关键字: references 外键表名(外键列名)
例子:
products 和 serial是一对多的关系
serial.pid字段是外键,products.id字段是外表对应的主键
--
-- 表的结构 `products`
--
DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(55),
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB;
--
-- 表的结构 `serial`
--
DROP TABLE IF EXISTS `serial`;
CREATE TABLE IF NOT EXISTS `serial` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(55) ,
`pid` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `pid` (`pid`),
CONSTRAINT `pidfk` FOREIGN KEY (`pid`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB ;
//可以修改表添加约束
mysql> alter table `serial` add constraint `keyname` foreign key(`pid`) references products(`id`) on delete cascade on update cascade;
其它
1, mysqldump在dump数据时,会加入外键信息
2, 也可以用SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';查看外键信息, 在comment列中
3, 注意innodb中如果设置了lower_case_table_names,对外键的影响
4, 在创建外键时,可以加入backtricks(反引号),将外键名,列名包括起来
5, 如果是在mysql3.23.50之前版本,注意如果表上有外键或者作为父表被refer,不要使用alter table , create index命令
6, Innodb要进行外键check时,需要在父或子表上加共享行级锁(shared row lock),不是事务级
7, 从mysql4.1.1开始,为了使reload dumpfile更容易进行,mysqldump生成的文件自动添加了FOREIGN_KEY_CHECKS=0选项以禁止外键约束检查
对于早期版本,可以用下面方法达到同样目的
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;
8, 在alter table或load data时,可能也需要进行上述设置以临时禁止外键约束检查
9, 除非设置了FOREIGN_KEY_CHECKS = 0, Innodb禁止drop父表(被子表外键引用的表)
10, 当设置FOREIGN_KEY_CHECKS = 0后drop一个父子.
下次重新创建这个父表时,必须跟drop之前一样(正确的列/数据类型/被参照的列上必须有索引), 否则系统会报1005 refer to errno 150 (这一点前述测试中没有发现报错