`
klcwt
  • 浏览: 188523 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

MySQL foreign Key

阅读更多
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. 总结
——-
引入外键的缺点是会使速度和性能下降,当然外键所带来的优点还有很多,本文仅讨论如何定义、删除外键。至于外键的实际应用将会在以后的文章中介绍。
分享到:
评论

相关推荐

    asp代码ASP家教信息管理系统(源代码+论文)

    asp代码ASP家教信息管理系统(源代码+论文)本资源系百度网盘分享地址

    基于ssm高校毕业选题管理系统.zip

    基于ssm高校毕业选题管理系统.zip

    基于旷视研究院领先的深度学习算法,提供满足多业务场景的预训练模型.zip

    人工智能毕业设计&课程设计

    tensorflow_model_optimization-0.1.3.dev0-py2.py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    tensorflow_model_analysis-0.15.0-py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    粒子群算法.docx 粒子群算法(Particle Swarm Optimization,PSO)是一种优化算法,受到鸟群或鱼

    粒子群算法 粒子群算法(Particle Swarm Optimization,PSO)是一种优化算法,受到鸟群或鱼群等群体行为的启发。该算法通过模拟群体中个体之间的合作和竞争来搜索最优解。粒子群算法通常用于解决连续优化问题。 ### 工作原理: 1. **初始化**:随机生成一群粒子(也称为个体),每个粒子代表搜索空间中的一个解,并随机初始化其位置和速度。 2. **评估**:根据每个粒子的位置,计算其对应的适应度值(目标函数值)。 3. **更新**:根据个体最优和全局最优的情况,更新每个粒子的速度和位置。粒子会根据自己历史最好的位置以及整个群体历史最好的位置进行调整,以期望更好的搜索方向。 4. **迭代**:重复评估和更新步骤,直到满足停止条件(如达到最大迭代次数、目标函数值足够接近最优解等)。 ### 主要参数: - 粒子数量(Population Size):群体中粒子的数量,通常越大越容易找到全局最优解,但计算成本也会增加。 - 惯性权重(Inertia Weight):控制粒子运动的惯性,平衡局部搜索和全局搜索能力。通常随着迭代次数增加而逐渐减小。

    20210327 AI-for-Drug-Discovery-2020.pdf

    20210327 AI-for-Drug-Discovery-2020

    tensorflow_model_optimization-0.1.2-py2.py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    Linux创建虚拟机的步骤

    Linux创建虚拟机的步骤

    基于SpringBoot的校园二手书交易管理系统设计源码

    这是一个基于SpringBoot开发的校园二手书交易管理系统,使用Java语言,包含102个文件。主要文件类型包括39个Java源文件、23个HTML文件、10个PNG图片文件、9个XML文件、9个JavaScript文件、4个CSS文件、2个Markdown文档、2个JPG图片文件、1个gitignore文件和1个SVG文件。该项目简洁易用,采用的技术经典,非常适合Java项目入门学习和企业级Java开发熟悉,提供了二手书交易管理、用户认证、数据统计等功能,旨在为校园内的二手书交易提供一个便捷、安全的平台。

    基于SSM的旅游管理系统.zip

    基于SSM的旅游管理系统.zip

    基于ssm框架网络财务设计与实现.zip

    基于ssm框架网络财务设计与实现.zip

    三菱PLC例程源码PLC同变频器通讯程序3

    三菱PLC例程源码PLC同变频器通讯程序3本资源系百度网盘分享地址

    基于ssm+jsp网上茶叶销售平台.zip

    基于ssm+jsp网上茶叶销售平台.zip

    通信专业毕业设计(论文)-企业网通信方案设计

    随着网络和科学技术的飞速发展,网络建设作为信息化建设的基础,也越来越受到企业的重视,网络结构和网络信息安全都是企业信息化建设中需要解决的重要问题。 本设计出于对众宇通讯公司长期稳定发展的考虑,针对公司的现状和发展需求,为公司设计了一个稳定的、相对安全的、可扩展并且可以支撑必要的网络应用的网络结构。在此次设计中,主要的运用到的技术与实现功能有:(1)汇聚交换机上使用DHCP技术,使各个接入层设备可自动获取相应的IP地址,也避免了IP地址的冲突;(2)运用VRRP技术,增强网络的连续性和稳定性,实现多链路备份冗余和网关备份冗余;(3)运用MSTP技术,将不同的VLAN与相应实例捆绑,避免了网络环路和广播风暴的产生;(4)通过防火墙技术,实现了企业内部与外部网络之间的信息交互安全。除此之外,还进行了VLAN的划分,端口安全设置,ACL访问限制,NAT地址转换,使用OSPF协议、静态路由等网络配置。 本论文基于华为ENSP仿真模拟软件,充分考虑到了整个公司网络今后的实用性、安全性以及可扩展性。利用所学的相关知识和网络技术,对众宇通讯公司的网络进行模拟设计。此设计根据三层网络结构来搭建网络拓扑,

    Gromacs中文手册5.0.2.pdf

    Gromacs中文手册5.0.2

    三菱PLC例程源码八层以下货梯通用程序(奥菱达)

    三菱PLC例程源码八层以下货梯通用程序(奥菱达)本资源系百度网盘分享地址

    seg.v

    seg.v

    ftqqzx.zip

    ftqqzx.zip

    基于tensorflow深度学习的中文机器阅读理解-完形填空.zip

    人工智能毕业设计&课程设计

Global site tag (gtag.js) - Google Analytics