mysql innodb 外键关联约束讲解

可通过查表信息查看外键的创建信息：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支持５种方式, 分列如下

 

事件触发限制:

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  (这一点前述测试中没有发现报错