外键为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)
注意：对于非InnoDB表，FOREIGN KEY子句会被忽略掉。
外键为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)
注意：对于非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)
以上就是MySQL定义外键的方法介绍。