mysql 外鍵約束

转载 2007年09月20日 08:52:00

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 设置外键约束(foreign key)

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

mysql添加外键约束-phpmyadmin

两种方法添加外键,1 终端sql语句添加 2.phpMyAdmin 可视化界面添加(也可以用workbench)下面介绍phpMyAdmin可视化界面添加:三个条件: 使用InnoDB引擎。 必须建立...

mysql外键约束

外键约束 维护一个学生与班级的关系   外键定义:外键就是指的是一个表的某个字段的值指向另外一个表的主键。   增加外键 在一张表中创建一个字段,该字段指向另外一张表的主键。通过一定的...

mysql外键约束详解

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

MySQL外键约束详解

最近在项目开发过程中常常遇到这样的情况,在写测试时,由于外键约束,我们在写测试sql的时候,不得不先在引用的表中插入相关数据,以避免数据操作时的错误。也许有时候是测试必须的,但大多时候,这个不是必须的...
  • JavaMoo
  • JavaMoo
  • 2017年03月11日 09:32
  • 422

MYSQL数据库(六)- 外键约束的参照操作

一、外键引用约束的参照操作其实可以简单理解为:在使用外键时候可以参照的依赖关系,例如父类删除子类也跟着删除。二、4种外键约束 1、cascade:从父表删除或者更新且自动删除或更新子表中匹配的行 2、...

MySQL外键约束删除时和更新时各取值的含义

1、准备测试工作 创建2个表,分别为studnet 学生表和monitor 班长表 学生表有学号和姓名2个属性; 班长表有班级和学号2个属性。 班长表设置外键学号,...

2016.11.10 MySQL外键约束不能建表

MySQL外键无法建表

Mysql外键约束的几种建立方式与区别

*Mysql外键约束的几种建立方式与区别*一.理论解释 外键约束的概念大概可以理解为对一张表的数据进行操作修改时受到另一张表相关联数据的影响。 ① 建立外键的语法大致如下:Fore...
  • dhrome
  • dhrome
  • 2017年06月01日 17:38
  • 224
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql 外鍵約束
举报原因:
原因补充:

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