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

Question

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 1

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.

Answer 2

Thanks a lot, I have been able to see the error with your instructions and have seen one foreign key from other table (direccion) which locks the creation of the table (provincia) because the column type for the foreign key was different from column type of the referenced table (provincia, which is going to create).

The table to create (provincia) was dropped before with problems solved with sudo mysqladmin -p flush-tables and the foreign keys pointing to it, still existed after drop.

I have dropped the foreign key from direccion to provincia and then have been able to create provincia. Then I have changed the column type in direccion and have recreated the foreign key.