MySQL表与varchar列作为外键

问题描述

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

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:

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;

我收到此错误：

[Err] 1215 - Cannot add foreign key constraint

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

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

推荐答案

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

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

 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

这篇关于MySQL表与varchar列作为外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！