外键为MySQL带来了诸多的好处，下面就为您介绍MySQL定义外键的语句写法，以及MySQL定义外键过程中出现错误的处理方法，供您参考学习。
复制
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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
注意：对于非InnoDB表，FOREIGN KEY子句会被忽略掉。#p#
如果遇到如下错误：
复制
ERROR 1005: Can’t create table ‘./test/articles.frm’ (errno: 150)
1.
请仔细检查以下定义语句，常见的错误一般都是表类型不是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`))
1.
2.
3.
4.
5.
6.
可见上面两条语句都会出现错误，因为在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)
1.
2.
以上就是MySQL定义外键的方法介绍。
【编辑推荐】
