MySQL FOREIGN KEY 分析和解决方法

在本文中，我们将介绍MySQL中 FOREIGN KEY 的使用，并重点讨论可能出现的错误：“Key column doesn’t exist in table”。我们会通过几个实例来说明这个错误常见的情况，以及如何解决这个问题。

阅读更多：MySQL 教程

FOREIGN KEY 概述

FOREIGN KEY 是关系型数据库中重要的约束之一，它用于建立表与表之间的关系。FOREIGN KEY 会关联到另外一个表的主键，在这个表中称作外键。通过FOREIGN KEY 约束的作用，可以保证数据之间完整性和一致性，还可以实现数据级联操作，提高了表之间的数据操作效率。

在 MySQL 中，要定义 FOREIGN KEY 约束，需要使用以下语法：

CREATE TABLE 表名(
  //字段定义
  FOREIGN KEY(字段名) REFERENCES 表名(主键)
)

这里，“表名”就是主表的表名，“字段名”则是指当前表中建立的外键字段名，“主键”则是指主表中的主键字段名。

KEY column doesn’t exist in table

“Key column doesn’t exist in table”是MySQL开发过程中常见的错误信息之一，在我们创建 FOREIGN KEY 约束时，经常出现这个错误。意思是“在表中不存在的关键字列”，指的是 FOREIGN KEY 约束中的列名在当前表中并不存在。

从错误提示来看，一般情况下，可能有以下几种出现该错误的情况：

1.外键表不存在；

2.主表中的主键字段定义名字错误；

3.外键表中的关联字段定义名字错误；

4.关联字段类型不一致；

5.关联字段宽度不一致。

下面分别讲解一下这些情况。

情况1：外键表不存在

当我们要建立一个 FOREIGN KEY 约束时，在定义外键表时，必须先保证外键表已经存在。否则，就会出现“Key column doesn’t exist in table”的错误。

例如，我们要在一个数据库中建立下列两张表（employee、department）之间的关系：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_id INT KEY
);

CREATE TABLE department
(
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

在employee表中，使用“dep_id”作为外键，同时它也是department表中的主键。因此，在employee表中使用FOREIGN KEY 约束（用于保持完整性），可以这样写：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_id INT KEY,
  FOREIGN KEY(dep_id) REFERENCES department(id)
);

CREATE TABLE department
(
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

这样，我们就定义了一个外键，让employee表中的“dep_id”指向department表的“id”。如果department表不存在，那么引用错误的情况就会出现。

情况2：主表中的主键字段定义名字错误

在第一篇介绍的代码中，如果定义PRIMARY KEY 的字段没有和FOREIGN KEY 的对应字段相同，则会出现上述错误。例如：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_id INT KEY,
  FOREIGN KEY(dep_id) REFERENCES department(no)
);

CREATE TABLE department
(
  no INT PRIMARY KEY,  //改动1
  name VARCHAR(20)
);

这个例子中，“dep_id”对应于department表中的主键“no”，但是，“no”也被定义为department表中的主键字段，如果在employee表中的FOREIGN KEY 约束中使用的是”id”字段，则会出现该错误。只需要更改代码如下：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_no INTKEY,
  FOREIGN KEY(dep_no) REFERENCES department(no)
);

CREATE TABLE department
(
  no INT PRIMARY KEY,
  name VARCHAR(20)
);

情况3：外键表中的关联字段定义名字错误

在上一个例子的基础上，还有一种情况，如果不仅主表的主键字段名字发生错误，而且外键表中的关联字段名字也错误地引用了错的字段名就会出现该错误。

例如，把上面两个表的定义稍作更改：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_no INT KEY,
  FOREIGN KEY(dep_id) REFERENCES department(no)  //改动1
);

CREATE TABLE department
(
  no INT PRIMARY KEY,
  name VARCHAR(20)
);

在这个例子中，“dep_no”是employee表中的外键字段，但是在FOREIGN KEY中引用的是“dep_id”，即使用了不存在的关键字列名。只要把它改为正确的列名就可以了：

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dep_no INT KEY,
  FOREIGN KEY(dep_no) REFERENCES department(no)  //改动2
);

CREATE TABLE department
(
  no INT PRIMARY KEY,
  name VARCHAR(20)
);

情况4：关联字段类型不一致

在 MySQL 中，如果你建立一个 FOREIGN KEY 的时候，如果关联的字段类型不一致，则会出现“Key column doesn’t exist in table”的错误。

例如，我们有两张表：order 和 customer，想在 order 表中使用 customer 表的主键作为外键，需要用到 FOREIGN KEY 约束。这个时候就需要注意数据类型的一致性了。

 CREATE TABLE customer
(
  id INT PRIMARY KEY,
  name VARCHAR(20),
);

CREATE TABLE order
(
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  customer_id INT,
  FOREIGN KEY(customer_id) REFERENCES customer(name)
);

在此例子中，customer 表的主键是 id，而 order 表使用了 name 字段来引用 customer 表的 id，显然两个类型不一致。可以将 order 表的 customer_id 改为 INT 类型，代码如下：

 CREATE TABLE order
(
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  customer_id INT,
  FOREIGN KEY(customer_id) REFERENCES customer(id)
);

情况5：关联字段宽度不一致

除了类型不一致外，在 MySQL 中还可以出现宽度不一致的情况。例如，在两个表：m_person 和 m_order 中，想用 person 表的 id 作为 order 表的外键：

CREATE TABLE m_person
(
  id VARCHAR(6) PRIMARY KEY,
  name VARCHAR(20)
);

CREATE TABLE m_order
(
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  person_id VARCHAR(10),
  FOREIGN KEY(person_id) REFERENCES m_person(id)
);

在这个例子中如果 m_person 表中的 id 是 VARCHAR 类型的话，而 m_order 表中使用了 INT 类型，则会出现“Key column doesn’t exist in table”错误。需要将m_order表中person_id的数据类型改成与m_person表中id的类型保持一致，代码如下：

CREATE TABLE m_order
(
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  person_id VARCHAR(6),
  FOREIGN KEY(person_id) REFERENCES m_person(id)
);

这样，我们就解决了出现“Key column doesn’t exist in table”错误的几种情况。

总结

通过对MySQL中FOREIGN KEY的介绍和常见错误 “Key column doesn’t exist in table”的分析，我们知道了如何正确定义FOREIGN KEY和如何避免不必要的错误。在实际开发中，我们应该尽量避免出现这个错误，以免影响数据的完整性和一致性。