`

MySQL InnoDB外键

阅读更多
从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支持5种方式, 分列如下

  . 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中,与SQL标准相违背的三点
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  (这一点前述测试中没有发现报错)
分享到:
评论

相关推荐

    Mysql增加外键的方法

    总之,通过上述的知识点介绍,我们可以了解到,在MySQL中建立外键并不复杂,关键是要确保数据表的存储引擎是InnoDB,并且要正确设置外键与主键的数据类型,同时还需要注意外键的触发限制条件。只有在实际的操作中...

    MySQL Innodb 索引原理详解

    - **外键支持**:InnoDB支持外键约束,而MyISAM不支持。 - **表锁机制**:MyISAM使用表锁机制,降低了并发性能。 #### 4. 总结 本文详细介绍了MySQL InnoDB存储引擎中的索引原理及其实现方式,特别是B+树的应用。...

    mysql的外键

    - 在InnoDB存储引擎中,外键约束是支持的;而在MyISAM存储引擎中,则不支持外键。 8. **实际应用** - 在电商系统中,订单表(Orders)可以通过外键关联用户表(Users),以标识下单的用户。 - 在论坛系统中,...

    mysql外键的添加与删除参考.pdf

    MySQL 中的外键只能在 InnoDB 存储引擎中生效,而其他存储引擎将忽略外键约束。 添加外键 --------- 在 MySQL 中,添加外键可以使用ALTER TABLE语句,例如: ``` ALTER TABLE ss_accesscode ADD FOREIGN KEY ...

    mysql处理添加外键时提示error 150 问题的解决方法

    MySQL 处理添加外键时提示 Error 150 问题的解决方法 MySQL 中添加外键时,可能会遇到 Error 150 的问题,这个问题是 MySQL 本身的 bug,已经被报告到 MySQL 开发者列表中很多年了。然而,导致这个错误的原因却并...

    MySQL中利用外键实现级联删除、更新

    外键在 MySQL 中的实现主要是通过 InnoDB 存储引擎来实现的。只有 InnoDB 引擎才允许使用外键,这是因为 InnoDB 引擎支持事务和锁机制,能够确保数据的一致性和完整性。 在创建外键时,要求父表必须有对应的索引,...

    MySQL体系结构及原理(innodb)图文完美解析

    InnoDB提供了多种高级功能,如外键约束、事务管理以及崩溃恢复机制等。 3. **MySQL与InnoDB的关系** MySQL可以使用多种不同的存储引擎,而InnoDB是其中最流行的一个。从MySQL 5.1版本开始,InnoDB被集成作为插件...

    去掉MYSQL脚本外键正则

    ### 去掉MySQL脚本外键正则表达式 #### 正则表达式的解释 给出的正则表达式为: ``` (?,\r\n.*CONSTRAINT[\s|\S]*?FOREIGNKEY[\s|\S]*?REFERENCES.*\)) ``` 该正则表达式主要用于匹配并捕获MySQL数据库脚本中的外键...

    mysql外键设置

    1. **存储引擎限制**:MySQL 的外键约束只支持 InnoDB 存储引擎,不支持 MyISAM 等其他类型的存储引擎。 2. **命名规则**:外键名称可以使用 `constraint 'fk_name'` 或 `constraint "fk_name"` 形式指定。 3. **...

    MySQL内核:InnoDB存储引擎 卷1.pdf.zip

    《MySQL内核:InnoDB存储引擎 卷1》是一本深度探讨MySQL数据库系统核心部分——InnoDB存储引擎的专业书籍。这本书以超高清的PDF格式呈现,包含详细的书签,便于读者快速定位和查阅相关内容。InnoDB作为MySQL中最常用...

    MySQL外键使用及说明详解

    两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持);  2.外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示...

    MySql Innodb 引擎特性详解

    6. **支持外键约束**:与MyISAM相比,InnoDB引擎支持外键约束,有助于维护数据库的完整性。 7. **不支持全文索引**:尽管InnoDB功能强大,但它不支持全文索引功能,这一点在特定的应用场景中可能需要额外考虑。 #...

    MySQL innodb 技术内幕

    InnoDB 存储引擎是 MySQL 的默认存储引擎,它支持外键、行锁、非锁定读、MVCC 等功能。InnoDB 存储引擎通过使用 MVCC 来获取高并发性,并且实现 SQL 标准的 4 种隔离级别,同时使用一种被称为 next-key locking 的...

    MySQL 和 InnoDB 性能

    MySQL支持多种存储引擎,其中最常用的是InnoDB,它提供了事务支持、行级锁定和外键约束等功能。 - **解析器与优化器**:负责将SQL语句解析为内部格式,并选择最优的执行计划。 - **执行器与缓存**:执行器根据优化后...

    mysql外键教程.zip

    MySQL数据库在设计关系型数据库时,外键是一个关键的概念,它用于维护数据库中表与表之间的关联性。这个“mysql外键教程.zip”压缩包包含了关于MySQL外键使用的多个方面,下面将详细解释这些知识点。 首先,我们...

    实战mysql innodb 删除数据后释放磁盘

    Innodb 删除数据后释放磁盘空间 InnoDB 是 MySQL 中的一种存储引擎,它具有高性能和...此外,InnoDB 还有其他一些特点,例如支持事务、锁机制、外键约束等。这使得 InnoDB 成为 MySQL 中的一种可靠和高效的存储引擎。

    MySQL innodb 8.0 新特性

    MySQL是一个广泛使用的开源关系型数据库管理系统,而InnoDB是MySQL的默认存储引擎,提供了事务处理、行级锁定和外键等特性。随着技术的不断进步,MySQL数据库也在不断地更新和改进。在MySQL 8.0版本中,InnoDB作为其...

Global site tag (gtag.js) - Google Analytics