Why is my unique constraint getting confused with a foreign key?

user_27457 0
Viewed 221

I have a schema with tables like this (simplified):

--------        ---------------        ------------
| key  |        | permission  |        | resource |
|------|        |-------------|        |----------|
| id   | -----< | id          | >----- | id       |
| name |        | key_id      |        | name     |
--------        | resource_id |        ------------
                | action      |
                ---------------

The permission table definition script is something like this:

CREATE TABLE `permission` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `key_id` INT(11) NOT NULL,
  `resource_id` INT(11) NOT NULL,
  `action` VARCHAR(32) NOT NULL,
  PRIMARY_KEY (`id`),
  CONSTRAINT `fk_permission_key` FOREIGN KEY (`key_id`) REFERENCES `key` (`id`),
  CONSTRAINT `fk_permission_resource` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This all works fine. Then, I decided I needed a unique constraint on the permission table so that only a single record can exist for an action/key/resource combination, so I do this:

ALTER TABLE `permission` ADD UNIQUE KEY `uq_permission` (`key_id`, `resource_id`, `action`);

This also works fine. We are in an environment where we use migrations to manage schema changes, so I want to make sure that there's a "roll back" script. But when I issue this command:

ALTER TABLE `permission` DROP INDEX `uq_permission`;

I get this error:

1553 - Cannot drop index 'uq_permission': needed in a foreign key constraint

After some fuddling around, I found that if I dropped the foreign key fk_permission_key, I am then able to drop the unique constraint.

Why is my unique constraint getting tangled up with a completely separate foreign key?

unique-constraint mysql
1 Answers

When you add the fk_permission_key constraint, MySQL automatically creates an index (this behaviour differs from other database engines, where you need to create such indexes explicitly):

mysql> SHOW INDEX FROM `permission`;
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| permission |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_key      |            1 | key_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_resource |            1 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

When you create the uq_permission index, MySQL apparently drops the fk_permission_key index because it understands it can use your own index.

mysql> SHOW INDEX FROM `permission`;
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| permission |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            1 | key_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            2 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            3 | action      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_resource |            1 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)

However, if you now attempt to remove uq_permission MySQL complaints because the foreign key will no longer have a usable index (and this time it isn't smart enough to create the index automatically).

I don't know if automatic index creation is configurable but, in this case, the only solution I can think of is to provide the index yourself:

mysql> ALTER TABLE `permission` ADD INDEX `fk_permission_key` (`key_id`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM `permission`;
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| permission |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            1 | key_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            2 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          0 | uq_permission          |            3 | action      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_resource |            1 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_key      |            1 | key_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)

mysql> ALTER TABLE `permission` DROP INDEX `uq_permission`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM `permission`;
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| permission |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_resource |            1 | resource_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| permission |          1 | fk_permission_key      |            1 | key_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Automatic index creation is documented:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

user_135080
Related