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, Mar 16 '17 at 17:28
I didn't say that. I said that this is usually how foreign keys are used, but it's not required. All that it requires is that the column in the other table has an index, it doesn't have to be the primary key. — Barmar, Mar 16 '17 at 17:33
The referenced column should be at least UNIQUE. That is not required by MySQL. But it's required by SQL standard and by the logic of relations. — Paul Spiegel, Mar 16 '17 at 17:43
We need to know what `category` looks like, asaproG. Present your [MCVE]. — Lightness Races in Orbit, Mar 16 '17 at 17:50
Also, read through [this manual page](https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) and ensure you're abiding by all the criteria. — Lightness Races in Orbit, Mar 16 '17 at 17:54
@PaulSpiegel you are right the problem was with the UNIQUE the fields not be UNIQUE — asaproG, Mar 16 '17 at 18:09

Rafiqul Islam · Answer 1 · 2017-03-16T20:05:02.670

-1

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

edited Mar 16 '17 at 20:05

answered Mar 16 '17 at 17:30

Rafiqul Islam

1,636
1
12
25

I try this but I get an error - "Cannot add foreign key constraint" – asaproG Mar 16 '17 at 17:31
Randomly changing which table you're trying to reference is likely to make things worse, not better. – Lightness Races in Orbit Mar 16 '17 at 17:54
I have change the answer check now – Rafiqul Islam Mar 16 '17 at 18:34
1

_"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."_ Where MySQL is concerned, this is only true for the `NDB` engine. The OP is using InnoDB, for which this is not true. Any compatible index will do. Read [the manual](https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) (However, I would advise a `UNIQUE` index anyway, as does the linked manual page) – Lightness Races in Orbit Mar 16 '17 at 18:48
[Can a foreign key reference a non-unique index?](http://stackoverflow.com/a/2179372/2151290) – Rafiqul Islam Mar 16 '17 at 20:06

Can't add foreign key in MySQL Database

1 Answers1