MySQL table with varchar column as foreign key

Question

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

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 request to fix it?

Answer 1

You can only have a foreign key referring to a unique field. Modify the network_classes table so that the category field is unique, for example 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; 

Then you can add the desired foreign key

Answer 2

column types in the table, but the referenced table does not match the restriction

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

Answer 3

The target of the FOREIGN KEY must be indexed. This is usually a PRIMARY KEY , so this is not a problem, but in your case it is not (although this is part of a composite key, this is not enough)

Create an index in the network_classes field. category :

 CREATE INDEX category_idx ON network_classes(category); 

Then recreate the networks table.

Answer 4

at

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

you used network_classess instead of network_classes (as in your create script table), so the table does not exist.

EDIT

The name of your restriction matches the change of key (network_fk1).

I better read your DDL.

Your network_classes table has a primary key identifier, so it’s correct to place the field for binding your id field as a foreign key, the category field should not appear in your network table, but I think you should put fk_network_class (as an int) associated with the identifier (network_classes )