`
晨星★~雨泪
  • 浏览: 448149 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

mysql 外鍵約束

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

相关推荐

    数据一致性守护者:MySQL外键约束深度解析

    ### 数据一致性守护者:MySQL 外键约束深度解析 #### 引言 在现代软件开发中,特别是Web应用程序领域,数据库扮演着极其重要的角色。其中MySQL作为一种流行的关系型数据库管理系统(RDBMS),因其开放源代码特性、...

    MySQL外键约束常见操作方法示例【查看、添加、修改、删除】

    在MySQL数据库中,外键约束是关系型数据库中用于维护数据完整性和一致性的重要工具。它确保了一张表中的数据与另一张表的数据之间的关联性,通常用于实现一对一、一对多或多对多的关系。本篇文章将详细讲解如何进行...

    详解MySQL 外键约束

    MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 2.外键的使用条件 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持) 外键列必须建立了索引,MySQL ...

    总结三种MySQL外键约束方式

    在数据库设计中,外键约束是确保数据完整性的重要机制,特别是在关系型数据库如MySQL中。外键用于建立两个表之间的关联,确保一个表中的数据(外键)引用另一个表(主表)中存在的数据(主键)。本文将详细介绍MySQL...

    MySQL外键约束的实例讲解

    MySQL外键约束是数据库设计中的一个重要概念,它用于在两个表之间建立联系,确保数据的一致性和完整性。外键约束的使用确保了当一个表(父表)中的数据发生更改时,与其关联的另一个表(子表)的相关数据也会相应...

    MySQL外键约束:数据引用完整性的守护神

    MySQL是一个流行的开源关系型数据库管理系统(RDBMS),广泛用于Web应用程序的后端数据存储。它基于结构化查询语言(SQL)来管理数据,并且是LAMP(Linux, Apache, MySQL, PHP/Python/Perl)技术栈的一部分,这个...

    MySQL删除表的时候忽略外键约束的简单实现

    在MySQL数据库管理中,删除表是一项需要谨慎操作的任务,尤其是当表之间存在外键约束时。外键约束是关系数据库中确保数据完整性和一致性的重要机制,它不允许在父表(引用其他表的字段的表)中删除那些已被子表引用...

    详解MySQL中的外键约束问题

    使用MySQL开发过数据库驱动的小型web应用程序的人都知道,对关系数据库的表进行创建、检索、更新和删除等操作都...MySQL外键约束条件  MySQL的外键约束条件有以下几种:  · CASCADE : 从父表删除或更新行时自动删除

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

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

    禁用启用所有外键约束.tst

    首先,让我们来看看如何在MySQL中禁用和启用外键约束: 1. **禁用外键约束**: 在MySQL中,可以使用以下SQL语句临时禁用外键约束: ```sql SET FOREIGN_KEY_CHECKS = 0; ``` 这行命令会关闭对当前会话中外键...

    MySQL的约束-外键约束

    在数据库设计中,数据完整性是至关...总结,外键约束是MySQL数据库设计中的关键组成部分,它保证了表之间的关联性,维持数据的完整性和一致性。理解并正确使用外键约束,对于构建健壮、可维护的数据库系统至关重要。

    MySQL外键约束的禁用与启用命令

    在MySQL数据库系统中,外键约束(Foreign Key Constraints)是一种重要的数据完整性机制,它确保了表之间的参照完整性,即一个表中的数据只能引用另一个表中存在的数据。外键约束可以防止非法数据的插入,保证数据库...

    MySQL外键的定义、作用、添加和删除

    为了保证数据的完整性,将两张表之间的数据建立关系,因此就需要在成绩表中添加外键约束。 外键的定义可以概括为:外键是指引用另外一个表中的一列或多列数据,被引用的列应该具有主键约束或者唯一性约束。外键用来...

    头歌MySQL数据库实训答案有目录.pdf

    * 外键约束(FOREIGN KEY):用于建立表之间的关系,例如 `CREATE TABLE t_student(id INT PRIMARY KEY, name VARCHAR(22), classId int, CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id));...

    mysql外键设置

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

    数据库外键约束方式

    在 MySQL 中,外键约束可以通过 FOREIGN KEY 语句来实现,例如: CREATE TABLE t_user ( id INT NOT NULL, name VARCHAR(30), groupid INT, PRIMARY KEY (id), FOREIGN KEY (groupid) REFERENCES t_group(id) ...

Global site tag (gtag.js) - Google Analytics