MySQL table with a varchar column as foreign key

Question

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?

neildt · Accepted Answer · 2013-08-22T15:17:44.347

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

score 10 · Answer 2 · answered Aug 22 '13 at 14:37

10

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.

answered Aug 22 '13 at 14:37

Ch Zeeshan

1,644
11
29

They both are using the same type (default type). How do you say that!!?? – Hamid Ghorashi Aug 22 '13 at 14:52
2

also worth mentioning, make sure they're both InnoDB tables – Nicola Beghin Nov 09 '17 at 14:52

score 3 · Answer 3 · answered Aug 22 '13 at 15:05

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.

score 0 · Answer 4 · answered Aug 19 '22 at 06:41

For me, it was the charset that was missing. Just providing an example for reference.

CREATE TABLE `client` (

id int NOT NULL, name varchar(255) NOT NULL, email varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, created_at datetime NOT NULL, created_by varchar(50) NOT NULL DEFAULT 'admin', updated_at datetime NOT NULL, updated_by varchar(50) NOT NULL DEFAULT 'admin', PRIMARY KEY (id), UNIQUE KEY name_UNIQUE (name), KEY name_idx (name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

create table `usecase` (`id` int NOT NULL AUTO_INCREMENT, `client` varchar(255) NOT NULL, `name` varchar(255), `description` varchar(1000), `rule_file` varchar(255), `parsed_rule_file` varchar(255), `archived` BOOLEAN DEFAULT 0, `state` ENUM('INITIATED','PARSED','UPLOADED', 'COMPLETE','FAILED'), `digest` varchar(255), `created_by` varchar(128) DEFAULT NULL, `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updated_by` varchar(128) DEFAULT NULL, `updated_at` datetime(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_usecase_id` (`client`,`name`), FOREIGN KEY (`client`) REFERENCES client(`name`))ENGINE=InnoDB DEFAULT CHARSET=latin1;

I had missed the line DEFAULT CHARSET=latin1

Joe Taras · Answer 5 · 2013-08-22T14:53:33.593

-1

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)

edited Aug 22 '13 at 14:53

answered Aug 22 '13 at 14:31

Joe Taras

15,166
7
42
55

Your network_classess is empty? Another thing, if you define a composite primary key, the foreign key should be equal (it's not impact on your error) – Joe Taras Aug 22 '13 at 14:39
yes, it is. can you please run the query on your side? I am looking for a practical solution rather than theoretical imaginations. – Hamid Ghorashi Aug 22 '13 at 14:56
I execute query on my PC and I have the same error. I change the foreign key (point to id of network_classes) together type of field (category become fk_network_class int), and it's OK – Joe Taras Aug 22 '13 at 14:59
I already tried the same thing that I found here: http://stackoverflow.com/questions/1285417/mysql-table-with-only-a-varchar-as-a-foreign-key comparing to my own sql nothig seems to be wrong. I ran it without any issue. so what is the difference in between? – Hamid Ghorashi Aug 22 '13 at 15:00
In that link user ask for about the possibility to create a foreign key with varchar type. This is legal! You try to link two table with a field not in primary key (category) is not correct about normalization of your db. – Joe Taras Aug 22 '13 at 15:10
Actually I want to link the category field in two tables to be updated simultaneously. I want to update category field in the child table once it is changed on the parent table. So I think there is no way other than programming. But I want to manage it by MySQL server without going through coding. – Hamid Ghorashi Aug 22 '13 at 15:29
Dear, use of foreign key provide your request. If you duplicate category field you can't update simultaneously (you must use a trigger or cascade policy). If you point to ID when your category (in parent table) changes, your child table will be updated – Joe Taras Aug 22 '13 at 15:35

MySQL table with a varchar column as foreign key

5 Answers5

Linked