Search code examples
mysqlforeign-keysinnodb

Error 1215: Can't add foreign key constraint (InnoDB)

I'm struggling around to create a foreign key with the following query:

alter table `users` add constraint `users_sales_partner_id_foreign` foreign key (`sales_partner_id`) references `structures` (`sales_partner_id`) on update cascade

InnoDB log says, it can't match this index:

2017-02-27 10:25:47 Error in foreign key constraint of table website_backend/users: foreign key (sales_partner_id) references structures (sales_partner_id) on update 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.

I have already checked typos and data type incompatibility, but everything seems to be alright:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `profile_id` int(10) unsigned NOT NULL,
  `sales_partner_id` int(11) NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` enum('pending','confirmed','active','deactivated') COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_profile_id_unique` (`profile_id`),
  UNIQUE KEY `users_sales_partner_id_unique` (`sales_partner_id`),
  UNIQUE KEY `users_email_unique` (`email`),
  CONSTRAINT `users_profile_id_foreign` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `structures` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sales_partner_id` int(11) NOT NULL,
  `sales_partner_structure` int(11) DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  `blocked` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I can't get my problem, does somebody have a clue? Thanks in advance!

Solution

  • Thanks to Paul Spiegel for his reminding that I can only set FKs to the indexed fields. After another review I noticed that my referenced field was not indexed, that solved my problem.