MySQL table with a varchar column as foreign key

问题:

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;

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?

回答1:

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

回答2:

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.

回答3:

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

Create an index on your network_classes.category field:

CREATE INDEX category_idx ON network_classes(category);

Then re-create your networks table.

回答4:

in

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

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

EDIT

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

I read better your 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)