BillB BillB - 4 months ago 12
MySQL Question

MySQL : Error Code: 1215. Cannot add foreign key constraint

I have two tables:

Table: utenti
Columns:
userId varchar(255) PK
password varchar(255)

Table: agenzie
Columns:
agenziaId varchar(255) PK
userId varchar(255)


When I try to create the foreign key on agenzie, this error message appear:

ALTER TABLE agenzie ADD FOREIGN KEY (userId) REFERENCES utenti(userId) Error Code: 1215. Cannot add foreign key constraint 0.015 sec


How can I fix that?

Thank you.

EDIT:

DROP TABLE IF EXISTS `utenti`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `utenti` (
`userId` varchar(255) CHARACTER SET latin1 NOT NULL,
`password` varchar(255) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `agenzie`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `agenzie` (
`agenziaId` varchar(255) NOT NULL,
`userId` varchar(255) NOT NULL,
PRIMARY KEY (`agenziaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer

You should be able to run:

show engine innodb status

and search for the phrase latest foreign key error (may be upper case).

That should provide more detail on why the constraint creation failed.

Of course, given the utter uselessness of the error message text Error Code: 1215. Cannot add foreign key constraint, you've got to wonder why the developers don't give you that information immediately, rather than forcing you to go looking for it.


Now that you've done that, and seen:

2015-02-19 00:51:55 1528 Error in foreign key constraint of table tesoreria/#sql-12a4_bd: FOREIGN KEY (userId) REFERENCES utenti(userId): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

It appears to be one of two things. The first is that there's no index incorporating utenti(userId) which is clearly not the case if it's a primary key.

The second is that the columns don't match and, based on your added DDL, it appears that may be the culprit, since the two columns are defined as:

`userId` varchar(255) CHARACTER SET latin1 NOT NULL,
`userId` varchar(255) NOT NULL,

In other words, it may be the presence of the character set specification making the columns a different type. I'd suggest creating them exactly the same type and seeing if that fixes the issue.