Odd Cannot add foreign key constraint

Question

I have an odd one. I cannot create a table using the following:

The table Users already exists in the DB, only UserTimeZones is to be added, and it fails.

 CREATE TABLE `Users` (
  `AccessFailedCount` int(11) NOT NULL,
  `EmailConfirmed` bit(1) NOT NULL,
  `Id` char(36) NOT NULL,
  `NormalizedUserName` varchar(256) DEFAULT NULL,
  `NormalizedEmail` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UserNameIndex` (`NormalizedUserName`),
  KEY `EmailIndex` (`NormalizedEmail`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `UserTimeZones` (
    `Id` char(36) NOT NULL,
    `UserId` char(36) NOT NULL,
    `TimeZoneOffsetInSeconds` int NOT NULL,
    `LastUpdatedAt` datetime(6) NOT NULL,
    CONSTRAINT `PK_UserTimeZones` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_UserTimeZones_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`) ON DELETE CASCADE
);

SHOW ENGINE INNODB STATUS;

Here is what the status shows:

------------------------ LATEST FOREIGN KEY ERROR

2018-11-09 11:26:44 0x7f832c523700 Error in foreign key constraint of table fifty/UserTimeZones:

FOREIGN KEY (UserId) REFERENCES Users (Id) ON DELETE CASCADE ):

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.

Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

So I have the classic "Cannot add foreign key constraint".

What I have tried:

• Placing the Users.Id column as first column : doesn't change anything
• The column types are the same, the engines too...
• Applying the migration without data in the DB -> it works
• Running the script in a DB without data -> still doesn't work...

What is the problem?

Not sure it matters but I use entity framework core.

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-key-constraints.html

Foreign key definitions for InnoDB tables are subject to the following conditions:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

But oddly the index seems to be needed on the referencing table:

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-keys-examples

So try adding an index on your table

CREATE TABLE `UserTimeZones` (
    `Id` char(36) NOT NULL,
    `UserId` char(36) NOT NULL,
    `TimeZoneOffsetInSeconds` int NOT NULL,
    `LastUpdatedAt` datetime(6) NOT NULL,
    CONSTRAINT `PK_UserTimeZones` PRIMARY KEY (`Id`),
    INDEX userid_ind (UserId),
    CONSTRAINT `FK_UserTimeZones_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`) ON DELETE CASCADE

);

Note: this is what the error message says.