注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

我的博客

 
 
 

日志

 
 

mysql数据表外键  

2011-08-14 18:01:47|  分类: 数据库类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

1. 什么是参照完整性?
——————–
参照完整性(完整性约束)是数据库设计中的一个重要概念,当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子:
文章分类表 -   categories
category_id      name
1                SQL Server
2                Oracle
3                PostgreSQL
5                SQLite

文章表 - articles
article_id       category_id      title
1                1                aa
2                2                bb
3                4                cc

可见以上两个表之间是通过category_id,其中categories表有4条记录,articles表有3条记录。

然而可能因为某种原因我们删掉了categories 表中category_id=4的记录,而articles表却还是有一条category_id=4的记录,很明显,category_id=4的这条记录不应该存在在articles表中,这样会很容易造成数据错乱。

相反,外键关系(Foreign Key relationships)讨论的是父表(categories)与子表(articles)的关系,通过引入外键(Foreign Key)这个概念来保证参照完整性(Referential integrity),将使会数据库变的非常简单。比如,要要做到删除categories表中category_id=4记录的同时删除 articles 表中category_id=4的所有记录,如果没有引入外键的话,我们就必须执行2条SQL语句才行;如果有外键的话,可以很容易的用一条SQL语句就可以达到要求。

2. 使用外键的条件
—————–
MySQL只在v3.23.34版本以后才引入外键的,所以在这之前的版本就别想了:),除此之外,还必须具备以下几个条件:

     1) 在my.cnf配置文件中打开InnoDB引擎支持。
     # Uncomment the following if you are using InnoDB tables
     innodb_data_home_dir = /var/db/mysql/
     innodb_data_file_path = ibdata1:10M:autoextend
     innodb_log_group_home_dir = /var/db/mysql/
     innodb_log_arch_dir = /var/db/mysql/

     2) 相关联的表都必须采用InnoDB引擎。

     3) 相关联的字段都必须建立所以。
     MySQL v4.0版本以后,定义外键时会自动建立所以,所以在 v4.0 版本以前(含v4.0版本)必须手工定义索引。

     4) 相关联的字段必须采用类似的数据类型,或者说可转换的数据类型,当然相同类型是最好不过了。
     比如父表的字段是TINYINT类型,则子表只能采用TINYINT、SMALLINT、INT、BIGINT等几种类型。

3. 外键语法参考
—————
可以通过 CREATE TABLE 或者 ALTER TABLE 来定义外键。
CREATE TABLE 语法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
     [(create_definition,…)]

create_definition:
     column_definition
   | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]

column_definition:
     col_name type [NOT NULL | NULL] [DEFAULT default_value]
         [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
         [COMMENT ’string’] [reference_definition]

index_col_name:
     col_name [(length)] [ASC | DESC]

reference_definition:
     REFERENCES tbl_name [(index_col_name,…)]
                [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
                [ON DELETE reference_option]
                [ON UPDATE reference_option]

reference_option:
     RESTRICT | CASCADE | SET NULL | NO ACTION

ALTER TABLE 语法:
ALTER [IGNORE] TABLE tbl_name
     alter_specification [, alter_specification] …

alter_specification:
   | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
   | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,…)
   | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]
   | DROP FOREIGN KEY fk_symbol

4. 定义外键
———–
mysql> CREATE TABLE categories (
     -> category_id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
     -> name varchar(30) NOT NULL,
     -> PRIMARY KEY(category_id)
     -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO categories VALUES (1, ‘SQL Server’), (2, ‘Oracle’), (3, ‘PostgreSQL’), (4, ‘MySQL’), (5, ‘SQLite’);
Query OK, 5 rows affected (0.48 sec)
Records: 5   Duplicates: 0   Warnings: 0

mysql> CREATE TABLE members (
     -> member_id INT(11) UNSIGNED NOT NULL,
     -> name VARCHAR(20) NOT NULL,
     -> PRIMARY KEY(member_id)
     -> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.55 sec)

mysql> INSERT INTO members VALUES (1, ‘test’), (2, ‘admin’);
Query OK, 2 rows affected (0.44 sec)
Records: 2   Duplicates: 0   Warnings: 0

mysql> CREATE TABLE articles (
     -> article_id INT(11) unsigned NOT NULL AUTO_INCREMENT,
     -> title varchar(255) NOT NULL,
     -> category_id tinyint(3) unsigned NOT NULL,
     -> member_id int(11) unsigned NOT NULL,
     -> INDEX (category_id),
     -> FOREIGN KEY (category_id) REFERENCES categories (category_id),
     -> CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id),
     -> PRIMARY KEY(article_id)
     -> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.63 sec)

注意:对于非InnoDB表,FOREIGN KEY子句会被忽略掉。
如果遇到如下错误:
ERROR 1005: Can’t create table ‘./test/articles.frm’ (errno: 150)
请仔细检查以下定义语句,常见的错误一般都是表类型不是INNODB、相关联的字段写错了、缺少索引等等。

至此categories.category_id和articles.category_id、members.member_id和 articles.member_id已经建立外键关系,只有 articles.category_id 的值存在与 categories.category_id 表中并且articles.member_id的值存在与members.member_id表中才会允许被插入或修改。例如:

mysql> INSERT INTO articles (category_id, member_id, title) VALUES (6, 1, ‘foo’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/articles`, CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category_id`)REFERENCES `categories` (`id`))

mysql> INSERT INTO articles (category_id, member_id, title) VALUES (3, 3, ‘foo’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/articles`, CONSTRAINT `fk_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`))

可见上面两条语句都会出现错误,因为在categories表中并没有category_id=6、members表中也没有member_id=3的记录,所以不能插入。而下面这条SQL语句就可以。

mysql> INSERT INTO articles (category_id, member_id, title) VALUES (3, 2, ‘bar’);
Query OK, 1 row affected (0.03 sec)

5. 删除外键定义
—————
不知道大家有没有发现,在前面定义外键的时候articles.member_id外键比articles.category_id子句多了一个CONSTRAINT fk_member ?
这个fk_member就是用来删除外键定义用的,如下所示:
mysql> ALTER TABLE articles DROP FOREIGN KEY fk_member;
Query OK, 1 row affected (0.25 sec)
Records: 1   Duplicates: 0   Warnings: 0

这样articles.member_id外键定义就被删除了,但是如果定义时没有指定CONSTRAINT fk_symbol (即外键符号)时该怎么删除呢?别急,没有指定时,MySQL会自己创建一个,可以通过以下命令查看:

mysql> SHOW CREATE TABLE articles;
+———-+————————————+
| Table     | Create Table                        |
+———-+————————————+
| articles | CREATE TABLE `articles` (
   `article_id` int(11) unsigned NOT NULL auto_increment,
   `category_id` tinyint(3) unsigned NOT NULL,
   `member_id` int(11) unsigned NOT NULL,
   `title` varchar(255) NOT NULL,
   PRIMARY KEY   (`article_id`),
   KEY `category_id` (`category_id`),
   KEY `member_id` (`member_id`),
   CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1           |
+———-+————————————+
1 row in set (0.01 sec)

可以看出articles.category_id的外键符号为articles_ibfk_1,因为就可以执行以下命令删除外键定义:

mysql> ALTER TABLE articles DROP FOREIGN KEY articles_ibfk_1;
Query OK, 1 row affected (0.66 sec)
Records: 1   Duplicates: 0   Warnings: 0

RESTRICT : 只要本表格里面有指向主表的数据, 在主表里面就无法删除相关记录。

一般来说,就选restrict最安全,当然也根据具体的业务而言了.cascade很cool,但是数据哗的一下就没了.

CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录,那么在此表里面的跟那个key一样的所有记录都会一同删掉。

为book表添加外键:

明确指定外键的名称:alter table book add constraint FK_BOOK foreign key(pubid) references pub_com(id) on delete restrict on update restrict;

不指定外键的名称,mysql会自动为你创建一个外键名称:alter table book add foreign key(pubid) references pub_com(id) on delete restrict on update restrict;

使用:show create table book;命令可查看

使用:desc book;可查看表结构

##########

说明,以上文章是参考网络资源的。个别的地方不够完善。

如于到下面的一种情况。

--
-- 限制导出的表
--
--
-- 限制表 `room_staus`
--
ALTER TABLE `room_staus` ADD CONSTRAINT `fk_rooms_roomtype` FOREIGN KEY ( `hotel_id` , `room_id` ) REFERENCES `room_type` ( `hotel_id` , `room_id` ) ON DELETE CASCADE ON UPDATE CASCADE ;

为mysql数据表添加外键(三)总结     

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

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

外键定义服从下列情况:

·         所有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 CASCADE
) 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 CASCADE 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 | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | 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 CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) 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。

 

6. 总结
——-
引入外键的缺点是会使速度和性能下降,当然外键所带来的优点还有很多,本文仅讨论如何定义、删除外键。至于外键的实际应用将会在以后的文章中介绍。

  评论这张
 
阅读(1771)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017