- 浏览: 441582 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
Luob.:
楼主 你这个问题怎么搞定的
奇异的 Spring WebApplicationContext 自动关闭 -
u013202238:
...
PDF和Java技术(PDFBox) -
u013202238:
...
PDF和Java技术(PDFBox) -
yuan1225:
您好请问 Discuz论坛的系统设置在哪里,登陆后台后没有 ...
Discuz!论坛通行证与JSP网站的整合 -
美妙人生:
,好,太好了,只是还是不太理解!
Discuz!论坛通行证与JSP网站的整合
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
6. 总结
——-
引入外键的缺点是会使速度和性能下降,当然外键所带来的优点还有很多,本文仅讨论如何定义、删除外键。至于外键的实际应用将会在以后的文章中介绍。
——————–
参照完整性(完整性约束)是数据库设计中的一个重要概念,当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子:
文章分类表 - 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
6. 总结
——-
引入外键的缺点是会使速度和性能下降,当然外键所带来的优点还有很多,本文仅讨论如何定义、删除外键。至于外键的实际应用将会在以后的文章中介绍。
发表评论
-
jquery 操作checkbox的例子(全选,反选,获取选取值)
2009-05-21 17:17 5239jquery 操作checkbox的例子(全选,反选,获取选取 ... -
struts2 捕获404错误
2008-11-28 22:04 5168当访问一个不存在页面时,一般显示404错误,这是一个很不友好的 ... -
mysql alter 语句用法,添加、修改、删除字段等
2008-11-06 12:15 1568mysql alter 语句用法,添加、修改、删除字段等 / ... -
mysql数据库自动定期备份的脚本
2008-07-30 15:51 2051说明: 保留每天备份的数据是件不太现实的事,做好的做 ... -
CVS的日常使用
2008-07-22 16:50 1455一个项目的首次导入 cvs ... -
Advanced SiteMesh
2008-07-14 11:40 1145假设你打算结合多种技 ... -
Discuz! 数据库结构参考(下)
2008-06-30 09:40 3110Discuz! 数据库结构参考 ... -
Discuz! 数据库结构参考(上)
2008-06-30 09:39 2008Discuz! 数据库结构参考(上) cdb_access-- ... -
mysql4.1以上版本连接时出现Client does not support authentic
2008-06-18 14:05 1233mysql4.1以上版本连接时出现Client does no ... -
Linux下两个不同版本Mysql的安装实战
2008-06-17 12:42 2769http://www.iteye.com/topic/2039 ... -
查看文本文件的编码格式
2008-06-13 17:16 3779Ubuntu enca命令可以用来查看文件的编码格式 安装 ... -
redhat9上用rpm包方式升级gcc3.2到gcc3.4
2008-06-11 09:50 4282刚刚安了个redhat9,发现gcc是3.2版本的,看到有文章 ... -
PHP+apache+mysql编程中乱码问题的解决
2008-06-10 17:20 2226作者: 乐道 2007-11 ... -
修改firefox 3的语言界面
2008-05-14 10:20 4727进入http://releases.mozilla.org/p ... -
Apache虚拟主机设置
2008-04-16 16:50 1847简介:这里说的虚拟主 ... -
Discuz!论坛通行证与JSP网站的整合
2008-04-11 16:43 5759最近做有关JSP与DISCUZ整合,搞了好久,还是搞不定,后来 ... -
PDF和Java技术(PDFBox)
2008-04-10 17:06 14693原文标题:Making PDFs Port ... -
Discuz 通行证的制作方法和详细说明
2008-04-09 17:42 2273通行证是个大题目,说白了也就是如何维护客户的会话,在php中所 ... -
使用Lucene2.3构建搜索引擎
2008-04-03 20:06 2560Lucene不是一个完整的全文索引应用,而是是一个用Java写 ... -
JTidy 协助抽取网页内容
2008-03-26 12:18 2666使用 JTidy 协助抽取网页内容Tidy 是 W3C 用来解 ...
相关推荐
主要介绍了MySQL外键约束常见操作方法,结合实例形式分析了mysql针对外键约束的查看、添加、修改、删除等相关操作实现方法,需要的朋友可以参考下
内包含外键约束模式,数据库的视图基本操作
这里以MySQL为例,总结一下3种外键约束方式的区别和联系。 这里以用户表和用户组表为例,这是一个典型的多对一关系,多个用户对应于一个用户组。 首先创建用户组表: create table t_group( id int ...
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 2.外键的使用条件 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持) 外键列必须建立了索引,MySQL ...
使用MySQL开发过数据库驱动的小型web应用程序的人都知道,对关系数据库的表进行创建、检索、更新和删除等操作都...MySQL外键约束条件 MySQL的外键约束条件有以下几种: · CASCADE : 从父表删除或更新行时自动删除
下面小编就为大家带来一篇MySQL外键约束的禁用与启用命令。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
MySQL的外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化。从这个特点来看,它主要是为了保证表数据的一致性和完整性的。 对于两个通过外键关联的表,相关联字段中主键所在的表是主...
MySQL的约束——外键约束。 一篇文章带你快速了解!
删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心。但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情;另外在测试的时候,也有需要重新创建数据库...
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件: 1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持); 2.外键列必须建立了索引,...
在MySQL中删除一张表或一条数据的时候,...禁用外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=0; 然后再删除数据 启动外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=1; 查看当前FOREIGN_KEY_CHECKS的值,可用
外键约束(FOREIGN KEY)
下面小编就为大家带来一篇MySQL 关闭子表的外键约束检察方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
很多朋友们在做表设计时,处理添加外键时提示error 150 问题,都会遇到此文档中详细的记载了解决方案,希望可以帮到你!
在MySql数据库建表时,如果CREATE TABLE语句中添加了FOREIGN KEY,那么所依赖的表必须先被创建,痛苦不堪,使用我这个工具可以将外键约束的语句分离出来,单独执行。 具体说明可以参考帖子 ...