mysql的外键约束

原创 2007年10月08日 15:09:00

InnoDB也支持外键约束。InnoDB中对外键约束定义的语法看起来如下:

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

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT |  | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT |  | 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子句的表定义。

当父表中的候选键被更新的时候,InnoDB支持同样选择。选择CASCADE,在子表中的外键列被设置为父表中候选键的新值。以同样的方式,如果在子表更新的列参考在另一个表中的外键,更新级联。

注意,InnoDB支持外键在一个表内引用,在这些情况下,子表实际上意味这在表内附属的记录。

InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。对外键的索引被自动创建。这是相对于一些老版本,在老版本中索引必须明确创建,否则外键约束的创建会失败。

在InnoDB内,外键里和被引用列里相应的列必须有类似的内部数据类型,以便它们不需类型转换就可被比较。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。如果你指定一个SET NULL动作,请确认你没有在子表中宣告该列为为NOT NULL

如果MySQL从CREATE TABLE语句报告一个错误号1005,并且错误信息字符串指向errno 150,这意思是因为一个外键约束被不正确形成,表创建失败。类似地,如果ALTER TABLE失败,且它指向errno 150, 那意味着对已变更的表,外键定义会被不正确的形成。你可以使用SHOW INNODB STATUS来显示一个对服务器上最近的InnoDB外键错误的详细解释。

注释:InnoDB不对那些外键或包含NULL列的被引用键值检查外键约束。

对SQL标准的背离:如果在父表内有数个行,其中有相同的被引用键值,然后InnoDB在外键检查中采取动作,就仿佛其它有相同键值的父行不存在一样。例如,如果你已定义一个RESTRICT类型的约束,并且有一个带数个父行的子行,InnoDB不允许任何对这些父行的删除。

居于对应外键约束的索引内的记录,InnoDB通过深度优先选法施行级联操作。

对SQL标准的背离: 如果ON UPDATE CASCADE或ON UPDATE SET NULL递归更新相同的表,之前在级联过程中该表一被更新过,它就象RESTRICT一样动作。这意味着你不能使用自引用ON UPDATE CASCADE或者ON UPDATE SET NULL操作。这将阻止级联更新导致的无限循环。另一方面,一个自引用的ON DELETE SET NULL是有可能的,就像一个自引用ON DELETE CASCADE一样。级联操作不可以被嵌套超过15层深。

对SQL标准的背离: 类似一般的MySQL,在一个插入,删除或更新许多行的SQL语句内,InnoDB逐行检查UNIQUE和FOREIGN KEY约束。按照SQL的标准,默认的行为应被延迟检查,即约束仅在整个SQL语句被处理之后才被检查。直到InnoDB实现延迟的约束检查之前,一些事情是不可能的,比如删除一个通过外键参考到自身的记录。

注释:当前,触发器不被级联外键的动作激活。

一个通过单列外键联系起父表和子表的简单例子如下:

CREATE TABLE parent(id INT NOT NULL,

                    PRIMARY KEY (id)

) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,

                   INDEX par_ind (parent_id),

                   FOREIGN KEY (parent_id) REFERENCES parent(id)

                     ON DELETE 

) TYPE=INNODB;

如下是一个更复杂的例子,其中一个product_order表对其它两个表有外键。一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,

                      price DECIMAL,

                      PRIMARY KEY(category, id)) TYPE=INNODB;

CREATE TABLE customer (id INT NOT NULL,

                      PRIMARY KEY (id)) TYPE=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  ON DELETE RESTRICT,

                      INDEX (customer_id),

                      FOREIGN KEY (customer_id)

                        REFERENCES customer(id)) TYPE=INNODB;

InnoDB允许你用ALTER TABLE往一个表中添加一个新的外键约束:

ALTER TABLE yourtablename

    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT |  | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT |  | SET NULL | NO ACTION}]

记住先创建需要的索引。你也可以用ALTER TABLE往一个表添加一个自引用外键约束。

InnoDB也支持使用ALTER TABLE来移除外键:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

当年创建一个外键之时,如果FOREIGN KEY子句包括一个CONSTRAINT名字,你可以引用那个名字来移除外键。另外,当外键被创建之时,fk_symbol值被InnoDB内部保证。当你想要移除一个外键之时,要找出标记,请使用SHOW CREATE TABLE语句。例子如下:

mysql> SHOW CREATE TABLE ibtest11c/G

*************************** 1. row ***************************

       Table: ibtest11c

Create Table: CREATE TABLE `ibtest11c` (

  `A` int(11) NOT NULL auto_increment,

  `D` int(11) NOT NULL default '0',

  `B` varchar(200) NOT NULL default '',

  `C` varchar(175) default NULL,

  PRIMARY KEY  (`A`,`D`,`B`),

  KEY `B` (`B`,`C`),

  KEY `C` (`C`),

  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)

REFERENCES `ibtest11a` (`A`, `D`)

ON DELETE  ON UPDATE ,

  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)

REFERENCES `ibtest11a` (`B`, `C`)

ON DELETE  ON UPDATE 

) ENGINE=INNODB CHARSET=latin1

1 row in set (0.01 sec)

 

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

InnoDB解析程序允许你在FOREIGN KEY ... REFERENCES ...子句中用`(backticks)把表和列名名字围起来。InnoDB解析程序也考虑到lower_case_table_names系统变量的设置。

InnoDB返回一个表的外键定义作为SHOW CREATE TABLE语句输出的一部分:

SHOW CREATE TABLE tbl_name;

从这个版本起,mysqldump也将表的正确定义生成到转储文件中,且并不忘记外键。

你可以如下对一个表显示外键约束:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

外键约束被列在输出的Comment列。

当执行外键检查之时,InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束,检查不对事务提交延迟。

要使得对有外键关系的表重新载入转储文件变得更容易,mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时,与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量:

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name;

mysql> SET FOREIGN_KEY_CHECKS = 1;

如果转储文件包含对外键是不正确顺序的表,这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0,对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。

InnoDB不允许你删除一个被FOREIGN KEY表约束引用的表,除非你做设置SET FOREIGN_KEY_CHECKS=0。当你移除一个表的时候,在它的创建语句里定义的约束也被移除。

如果你重新创建一个被移除的表,它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型,并且如前所述,它必须对被引用的键有索引。如果这些不被满足,MySQL返回错误号1005 并在错误信息字符串中指向errno 150。 

 

总结mysql的三种外键约束方式

如果表A的主关键字是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常...
  • qq_34123420
  • qq_34123420
  • 2016-09-13 23:11:20
  • 9843

mysql建立外键约束

MySQL创建关联表可以理解为是两个表之间有个外键关系,但这两个表必须满足三个条件 1.两个表必须是InnoDB数据引擎 2.使用在外键关系的域必须为索引型(Index) 3.使用在外键关系的域必须与...
  • lcg910978041
  • lcg910978041
  • 2016-07-25 18:11:51
  • 2475

Mysql 为表添加外键约束

Mysql 为表添加外键约束 Error Code: 1215. Cannot add foreign key constraint (foreign keys)
  • u013905744
  • u013905744
  • 2017-05-08 09:30:31
  • 5718

mysql 设置外键约束(foreign key)

建立外键约束可以采用列级约束语法和表级约束语法,如果仅仅对单独的一个数据列建立外键约束,则使用列级约束语法即可;如果需要对多个列组合创建外键约束,或者需要为外键约束指定名字则必须使用表级约束语法。 ...
  • cherry_na
  • cherry_na
  • 2015-02-06 13:43:13
  • 16779

外键及其约束理解

数据库外键的详解,同时以MySQL数据库举例子解释外键及其约束的设置等
  • u012557538
  • u012557538
  • 2015-03-01 14:32:51
  • 15118

MYSql对外键约束及字段的一些操作

最近使用JPA项目自动生成数据表,实体类的属性修改过,对应的数据表也增加了相关的字段。现在要删掉多余的字段。 1、mysql -uroot -proot --登录 2、set charset gb...
  • herestay
  • herestay
  • 2013-02-04 09:07:56
  • 321

MySql入门之一:DDL操作创建表(添加主键, 外键约束以及基本的数据类型)

创建外键,声明主键的方式小结,mysql,主键,外键,数据类型,5.mysql 数据类型
  • duoluodezhu0826
  • duoluodezhu0826
  • 2017-03-05 13:01:01
  • 3914

如何在MySQL中设置外键约束以及外键的作用

如何在MySQL中设置外键约束以及外键的作用 1.外键的作用,主要有两个: 一个是让数据库自己通过外键来保证数据的完整性和一致性 一个就是能够增加ER图的可读性 2.外键的配置 ...
  • bestlove12345
  • bestlove12345
  • 2016-08-05 10:58:00
  • 23188

为mysql数据表添加外键(一)

1. 什么是参照完整性?——————–参照完整性(完整性约束)是数据库设计中的一个重要概念,当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子:文章分类表 -   cat...
  • hongsejiaozhu
  • hongsejiaozhu
  • 2007-10-08 15:30:00
  • 10819

mysql如何添加一个表的外键

1:创建一个父表,主键作为子表的外键: 1 create table province( 2 pId int primary key auto_increment, 3 pNam...
  • Biexiansheng
  • Biexiansheng
  • 2017-08-21 16:26:55
  • 3209
收藏助手
不良信息举报
您举报文章:mysql的外键约束
举报原因:
原因补充:

(最多只允许输入30个字)