CODEWITHSUNDEEP
mysql
bln_dev
bln_dev

Reputation: 2411

Error "Cannot add foreign key constraint" without trying to create a foreign key

I have this Query:

CREATE TABLE `team` (
  `id` int(11) NOT NULL  
);

/* SQL Error (1215): Cannot add foreign key constraint */

which clearly does not contain a foreign key declaration. So all answers I am finding online are not for me. The reason this error occurs must have been related to a MySQL caching issue. Because the table existed previously and I deleted it. Thus, renaming the table name in the create table command to teams creates the table just fine.

My question is, where does mysql store this cache and how can I delete it. In information_schema I cannot find it. in information_schema.INNODB_TABLES the table is no longer listed.

Update 1

Before deleting the table team it was created with foreign keys, with:

CREATE TABLE `team` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_verified` tinyint(1) NOT NULL,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `foreign_uuid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `team`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `UNIQ_C4E0A61FD17F50A6` (`uuid`);

But now the error occurs with just the first (very simple) query.

Update 2

I tried

mysql> FLUSH LOGS;
mysql> RESET MASTER;

to no avail.

Update 3

After restarting the mysql service the error changed more concrete to:

CREATE TABLE `team` (
  `id` int(11) NOT NULL
);
/* SQL Error (1822): Failed to add the foreign key constraint. Missing index for constraint 'FK_6C66F57B296CD8AE' in the referenced table 'team' */

Upvotes: 0

Views: 122

Answers (1)

Solarflare
Solarflare

Reputation: 11106

When you are disabling foreign_key_checks, it allows you to all kind of things, especially

Setting foreign_key_checks to 0 also affects data definition statements: [...] DROP TABLE drops tables that have foreign keys that are referred to by other tables.

That's what you (or someone you can blame) did.

When you recreate the table, you need to make sure that the referenced constraints are technically valid (even if you keep foreign_key_checks disabled):

When re-creating a table that was dropped, an error is returned if the table definition does not conform to the foreign key constraints referencing the table.

The exact error you get depends a bit on what version you are using, for MySQL 5.5 it would be Error Code: 1005. Can't create table 'tablename' (errno: 150), since MySQL 5.6, the error message is

SQL Error (1215): Cannot add foreign key constraint

So the problem here is that another table is referencing your table with a foreign key constraint, but the new table definition doesn't fit.

A fairly easy way to find the culprit is to use show engine innodb status, it will contain, amongst other things, useful details in the LATEST FOREIGN KEY ERROR section, including the foreign key definition and the name of table. Alternatively, and especially if you suspect to have more than one foreign key problem, have a look at How do I see all foreign keys to a table or column? (which is where MySQL stores that information, although it's not a cache that you can clear).

While you can include the referenced column into your new table, it doesn't look as if you intend to honor that constraint anymore, so you probably need to drop the referencing foreign key (or table).

Upvotes: 1

Related Questions