Can't add foreign key in MySQL Database

Question

Trying to create a simple relation between two fields in two tables - 'Task' table with the field 'USER_TOKEN' and the 'USER' table with the field 'TOKEN'. The two fields are the same structure. As you can see the error and other things that may assist you to help me understand the problem and fix it.

System: MacOS 10.12.3 | DB : MySQL 5.7.17 | DBM : Sequel Pro

Error : MySQL said: Cannot add foreign key constraint

CREATE TABLE `TASK` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `USER_TOKEN` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `USER` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `TOKEN` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE USER
ADD CONSTRAINT TOKENS
FOREIGN KEY (`TOKEN`) REFERENCES `category`(`USER_TOKEN`)

Thanks.

In the category table, make sure you have an index on the USER_TOKEN column. But usually foreign keys reference the primary key of another table. — Barmar

Rafiqul Islam Rafiqul Islam · Accepted Answer · 2017-03-16T17:30:13

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

So your USER_TOKEN column of table TASK and TOKEN column of USER table must be UNIQUE. So run the following query:

CREATE TABLE `TASK` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`USER_TOKEN` int(11) unsigned NOT NULL UNIQUE,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `USER` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`TOKEN` int(11) unsigned NOT NULL UNIQUE,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `USER`
ADD CONSTRAINT `TOKENS`
FOREIGN KEY (`TOKEN`) REFERENCES `TASK`(`USER_TOKEN`);

Check Demo here

Can't add foreign key in MySQL Database

1 Answers