以 varchar 列作为外键的 MySQL 表

MySQL table with a varchar column as foreign key

提问by Hamid Ghorashi

I am trying to create a table with a varchar column as foreign key but MySql gives me an error while creating the table. My query is like this:

我正在尝试创建一个带有 varchar 列作为外键的表，但是 MySql 在创建表时给了我一个错误。我的查询是这样的：

CREATE TABLE network_classes (
    id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    category VARCHAR(80) NOT NULL,
    PRIMARY KEY(id),
    KEY `key_1` (`id`,`category`)
)
ENGINE=InnoDB;


CREATE TABLE networks (
    id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(80) NOT NULL,
    director_id TINYINT(3) UNSIGNED NULL,
    director_name VARCHAR(100) NULL,
    description VARCHAR(1000) NULL,
    last_modified TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    user_id SMALLINT UNSIGNED NULL,
    PRIMARY KEY(id),
    KEY `networks_fk1` (`category`),
    CONSTRAINT `networks_fk1` FOREIGN KEY (`category`) REFERENCES `network_classes` (`category`) ON DELETE NO ACTION,
    INDEX networks_index2471(name),
    INDEX networks_index2472(director_id, director_name)
)
ENGINE=InnoDB;

and I get this error:

我收到这个错误：

[Err] 1215 - Cannot add foreign key constraint

I am using MySQL 5.6.12. How can I rewrite my query to fix it?

我正在使用 MySQL 5.6.12。如何重写我的查询来修复它？

回答by neildt

You can only have a foreign key referencing a unique field. Modify your network_classes table so that the category field is unique, like below

您只能有一个引用唯一字段的外键。修改您的 network_classes 表，使类别字段是唯一的，如下所示

 CREATE TABLE network_classes (
    id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    category VARCHAR(80) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY `category_UNIQUE` (`category`),
    KEY `key_1` (`id`,`category`)
)
ENGINE=InnoDB;


CREATE TABLE networks (
    id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(80) NOT NULL,
    director_id TINYINT(3) UNSIGNED NULL,
    director_name VARCHAR(100) NULL,
    description VARCHAR(1000) NULL,
    last_modified TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    user_id SMALLINT UNSIGNED NULL,
    PRIMARY KEY(id),
    KEY `networks_fk1` (`category`),
    CONSTRAINT `networks_fk1` FOREIGN KEY (`category`) REFERENCES `network_classes` (`category`) ON DELETE NO ACTION,
    INDEX networks_index2471(name),
    INDEX networks_index2472(director_id, director_name)
)
ENGINE=InnoDB;

You should then be able to add the foreign key you want

然后你应该能够添加你想要的外键

回答by Ch Zeeshan

column types in the table and the referenced table do not match for constraint

表和引用表中的列类型与约束不匹配

Why 2 varchar columns with same size not match in type? And of course the answer is obvious collation. Turns out that in the new table the column was UTF-8 instead of ASCII as in the referenced table. Changed to ascii and done.

为什么 2 个大小相同的 varchar 列在类型上不匹配？当然，答案是明显的整理。事实证明，在新表中，列是 UTF-8 而不是引用表中的 ASCII。更改为 ascii 并完成。

回答by Jeremy Smyth

The target of a FOREIGN KEYconstraint needs to be indexed. Usually, it is a PRIMARY KEYso this isn't an issue, but in your case it's not (although it's part of a composite key, that's not enough)

FOREIGN KEY约束的目标需要被索引。通常，它是一个PRIMARY KEY所以这不是问题，但在您的情况下不是（尽管它是复合键的一部分，但这还不够）

Create an index on your network_classes.categoryfield:

在您的network_classes. category场地：

CREATE INDEX category_idx ON network_classes(category);

Then re-create your networkstable.

然后重新创建您的networks表。

回答by Joe Taras

in

在

CONSTRAINT `networks_fk1` FOREIGN KEY (`category`)
REFERENCES `network_classess` (`category`) ON DELETE NO ACTION,

you have used network_classessinstead of network_classes(as in your create table script), so that table not exists.

您使用了network_classess而不是network_classes（如在您的创建表脚本中），因此该表不存在。

EDIT

编辑

Name of your constraint is the same of key (networks_fk1) change ones.

您的约束名称与键 (networks_fk1) 更改名称相同。

I read better your DDL.

我读得更好你的DDL。

Your table network_classes has a primary key ID, so is correct put as foreign key a field to link your id field, the category field mustn't appear in your table network, but I think you must put fk_network_class (as int) linked to id (of network_classes)

您的表 network_classes 有一个主键 ID，因此将外键作为链接您的 id 字段的字段是正确的，类别字段不能出现在您的表网络中，但我认为您必须将 fk_network_class（作为 int）链接到 id （网络类）