MySQL InnoDB外键

qq78707

从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}]


外键约束须满足以下条件:
. 父子表都为innodb,不是临时表

. 在子表上,必须存在一个索引,外键列是索引列的全部或部分,但必须是开头部分,并且顺序一致; 从mysql4.1.2开始,如果不存在索引,会自动创建外键上的索引; 加速约束检查/避免全表扫描

. 在父表上,必须存在一个索引,被参照键是索引列的全部或部分,但必须是开头部分,并且顺序一致; 加速约束检查/避免全表扫描

. 不允许在外键前缀或后缀上索引; 外键不能包括text/blob列

. 如果指定约束标识符 symbol, 那么必须数据库范围唯一; 不指定时,系统会自动产生

. 父子表的相应列必须有相似的内部数据类型, 以便进行比较时不必进和类型转换

   对于数字类型，类型长度与符号必须相同;
   对于字符类型, 长度不必相同

. 如果创建一个on delete set null或on update set null约束，子表的列必须不能为not null

. 从mysql3.23.50开始，如果外键或候选键(被引用键)列上有null值，mysql 将不进行check


外键约束对子表的含义:
  如果在父表中找不到候选键,则不允许在子表上进行insert/update

外键约束对父表的含义:
  在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句, InnoDB支持５种方式, 分列如下

  . cascade方式
   在父表上update/delete记录时，同步update/delete掉子表的匹配记录
   On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用

  . set null方式
   在父表上update/delete记录时，将子表上匹配记录的列设为null
   要注意子表的外键列不能为not null
   On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用

  . No action方式
   如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
   这个是ANSI SQL-92标准,从mysql4.0.8开始支持

  . Restrict方式
   同no action, 都是立即检查外键约束

  . Set default方式
   解析器认识这个action,但Innodb不能识别，不知道是什么意思．．．

  注意：trigger不会受外键cascade行为的影响,即不会解发trigger

在mysql中，与ＳＱＬ标准相违背的三点
1.       如果在父表中有多个key值相同，那么在进行外键check时,会当成有相同key值的其他行不存在; 比如当定义了一个restrict行为外键时,一个子表行对应到多个父表行(具有相同key值), Innodb不允许删除父表上的所有这些行

下面这句未理解,depth-first?
InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

2.       父子表是同一个表,自我参照时不允许指定on update cascade, on update set null
从mysql4.0.13开始，允许同一个表上的on delete set null
从mysql4.0.21开始，允许同一个表上的on delete cascade
但级联层次不能超出15

3, Innodb在检查unique,constraint约束时，是row by row而不是语句或事务结束;
  SQL标准中对constraint的检查是在语句执行完成时


创建/操作外键的例子
--简单外键
CREATE TABLE parent
(
id INT NOT NULL,
PRIMARY KEY (id)
)
engine=INNODB;

CREATE TABLE child
(
   id INT,
   parent_id INT,
   INDEX par_ind (parent_id,id),  è外键列是索引列的开头部分
   FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
)
engine=INNODB;

--较复杂的外键
CREATE TABLE product
(
   category INT NOT NULL,
   id INT NOT NULL,
   price DECIMAL,
   PRIMARY KEY(category, id)
)
engine=INNODB;

CREATE TABLE customer
(
  id INT NOT NULL,
  PRIMARY KEY (id)
)
engine=INNODB;

CREATE TABLE product_order
(
   no INT NOT NULL AUTO_INCREMENT,
   product_category INT NOT NULL,
   product_id INT NOT NULL,
   customer_id INT NOT NULL,
   PRIMARY KEY(no),
   INDEX (product_category, product_id),
   FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,
   INDEX (customer_id),
   FOREIGN KEY (customer_id)REFERENCES customer(id)
)
engine=INNODB;

--查看外建/drop外键/alter 添加外键
mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) default NULL,
  `parent_id` int(11) default NULL,
  KEY `par_ind` (`parent_id`,`id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table child drop FOREIGN KEY child_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table child add constraint child_ibfk_1
    -> FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


Mysql中与外键相关的错误

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message
string refers to errno 150, this means that the table creation failed because a foreign key constraint
was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that
means a foreign key definition would be incorrectly formed for the altered table. Starting from
MySQL 4.0.13, you can use SHOW INNODB STATUS to display a detailed explanation of the
latest InnoDB foreign key error in the server.

测试如下：
set FOREIGN_KEY_CHECKS = 0; --禁用约束检查
drop table product;  --删除product_order的父表
set FOREIGN_KEY_CHECKS = 1; --启用约束检查
重新创建product,这次没有建pk
CREATE TABLE product
(
   category INT NOT NULL,
   id INT NOT NULL,
   price DECIMAL,
   index(category, id)
)
engine=INNODB;

查看show innodb status中的错误信息
------------------------
LATEST FOREIGN KEY ERROR
------------------------
080424 19:38:50  Cannot drop table `test/product`
because it is referenced by `test/product_order`

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