There are two tables. I deleted all the records from them. And we need to create a foreign key constraint, over the field auth_item :: name and modules :: main_role_name; In table 2, I create an attempt to make a foreign key constraint, but it gives an error. Although both fields are of the same type and size, they can be NULL, and all records from both tables are deleted.

Ошибка: #1215 - Cannot add foreign key constraint

How to see why the key is not created? Or what could be the reasons if mysql doesn't report this?

The request itself is as follows:

 ALTER TABLE `modules` ADD FOREIGN KEY (`main_role_name`) REFERENCES `auth_item`(`name`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Index on the field modules :: main_role_name is. And the auth_item :: name field is the primary key.

DDL tables:

 | auth_item | CREATE TABLE `auth_item` ( `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `type` int(11) NOT NULL, `description` text COLLATE utf8_unicode_ci, `rule_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `data` text COLLATE utf8_unicode_ci, `module_id` int(11) DEFAULT NULL, `created_at` int(11) DEFAULT NULL, `updated_at` int(11) DEFAULT NULL, `level` int(1) NOT NULL DEFAULT '0', `main_permission` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`name`), KEY `rule_name` (`rule_name`), KEY `idx-auth_item-type` (`type`), KEY `module_id` (`module_id`), KEY `idx-auth_item-main_permission` (`main_permission`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | | modules | CREATE TABLE `modules` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `orders` int(11) NOT NULL DEFAULT '0', `code` varchar(15) NOT NULL, `main_role_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx-modules-main_role_name` (`main_role_name`) ) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT |
  • Well, the query, which creates FK - where? - Akina
  • @Akina edited the question - Maksat Orunkhanov
  • Fine. I hope in the modules structure of the table there is a field main_role_name , and in the structure of the table auth_item is an index in the field name ? - Akina
  • @Akina Yes, it is. - Maksat Orunkhanov September
  • one
    COLLATION MUST be the same. As for the size - if the "tail" beyond 64 characters is unimportant for binding, then you can explicitly create an index on the first 64 characters of the field on modules, and use it for binding. - Akina

1 answer 1

As @Akina wrote in the comments:

COLLATION MUST be the same. As for the size - if the "tail" beyond 64 characters is unimportant for binding, then you can explicitly create an index on the first 64 characters of the field on modules, and use it for binding.