MySQL Error: Cannot add foreign key constraint.

So today at work, I needed to add a foreign key constraint to a new database table.

I proceeded as I normally would and thought that there’d be zero issues. Boom, I was greeted by a not-so-helpful MySQL error message: “Cannot add foreign key constraint”.

What does that even mean? I first thought it was a syntax error in my MySQL query, but seems it was not - what was going on?

The database table structure

The table structure that I was working with looked something like this:

CREATE TABLE `groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `pivot` (
  `groups_id` bigint(20) unsigned NOT NULL,
  `products_id` bigint(20) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` int(4) NOT NULL DEFAULT '0',
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

The idea was to add a foreign key constraint to the pivot table so that whenever a product was deleted, it would remove all the relationships with the group entity. Also, when a group was deleted it should remove all entries that were linked to that specific group in the pivot table.

ALTER TABLE pivot ADD FOREIGN KEY (groups_id) REFERENCES groups(id) ON DELETE CASCADE;

Alright, that went just as it should, the issue was when I added the same foreign key constraint to the products_id column in the pivot table.

ALTER TABLE pivot ADD FOREIGN KEY (products_id) REFERENCES products(id) ON DELETE CASCADE;

So after some debugging, I found the reason for this “Cannot add foreign key constraint” error. It turns out that it was because of the size of the products.id and pivot.products_id columns were not the same.

There are some requirements for when you add foreign key constraints. They have to be the same type, it’s not enough that they’re both integers. They also have to have the same signedness, one can’t be unsigned while the other column is signed. They also have to have the same encoding and collation for them to work.

Index name conflicts

As a reminder and a heads-up, it’s important to know that all indexes are stored in the same information schema table. That means that you have to choose a unique name for your foreign key constraints within the same schema or database. Failing to do so will give you an error that there’s a name conflict.

To fetch all the names of your foreign key constraints, run the following query:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA IS NOT NULL;

That’s it for this time, I hope that this saves some time debugging for someone out there in a similar situation that I was in.

Until next time, have a good one!