Mysql – Foreign key subsumed by unique constraint in MariaDB/MySQL

foreign keymariadbMySQLunique-constraint

I'm using a "migrations" approach to upgrading/downgrading table structure (Laravel). Essentially tables are added and altered as the application grows, but each "upgrade" script has an appropriate "downgrade" script to revert the changes and restore the structure to the previous version.

I want to implement the relationship "All actors are users, but not all users are actors" (typical one-to-zero-or-one relationship). I do this by creating a foreign key from actors to users, then adding a unique constraint to the FK column.

The problem: When dropping the unique constraint (to "downgrade" to the previous version), I get an error that the constraint is needed by the FK. This happens no matter whether there is data in any of the tables or not, and happens in both MySQL and in MariaDB. What am I missing here? As much as I know, this is the correct way to implement one-to-zero-or-one relationships…

Minimum working example:

App version 1 ("We only have users"):

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded, v.1.5 ("We have users AND actors, but each actor must have a user account to login").

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded to version 2 ("Each actor may only have one user account"). A one-to-zero-or-one relationship needs to be enforced:

ALTER TABLE `actors` ADD CONSTRAINT this_subsumes_fk UNIQUE (`user_id`);

During testing, we need to revert to version 1.5, where the unique index was not there yet:

alter table `actors` drop index this_subsumes_fk;

And here's the error from dropping the index:

Cannot drop index 'this_subsumes_fk': needed in a foreign key constraint

The "Show create table.." output at this point is the following; both the unique constraint and the FK are there!!

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `this_subsumes_fk` (`user_id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Best Answer

I may have stumbled on a solution by trial and error -- correct me if I'm wrong please.

Seems that the FK needs some form of index to function. When creating the unique index, the FK's own index is taken over by the unique one, so that when the unique index is dropped later, the FK would remain "index-less". To prevent that from happening, seems like it is enough to add a normal index before deleting the unique:

ALTER TABLE `actors` ADD index (`user_id`);
alter table `actors` drop index this_subsumes_fk;

At least, with this I don't get an error anymore.

Related Question