MySQL: ERROR 1553 (HY000): Cannot drop index ‘uniq_profit’: needed in a foreign key constraint

1
2
3

  UNIQUE KEY `uniq_profit` (`sales_id`,`accounting_status`),
  CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`),
  CONSTRAINT `costs_profit_chk_1` CHECK ((`fiscal_year` >= 0))

1
2

ALTER TABLE `costs_profit` DROP FOREIGN KEY `costs_profit_sales_id_d56dec15_fk_sales_sales_id`, DROP INDEX `uniq_profit`;
ALTER TABLE `costs_profit` ADD CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`);

実行:

1
2
3
4
5
6
7

mysql> ALTER TABLE `costs_profit` DROP FOREIGN KEY `costs_profit_sales_id_d56dec15_fk_sales_sales_id`, DROP INDEX `uniq_profit`;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `costs_profit` ADD CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`);
Query OK, 16 rows affected (0.17 sec)
Records: 16  Duplicates: 0  Warnings: 0

Django Migration の失敗

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('partners', '0003_auto_20230426_0908'),
    ]

    operations = [
        migrations.RemoveConstraint(
            model_name='partneruserrole',
            name='uniq_partneruserrole',
        ),
        migrations.AddConstraint(
            model_name='partneruserrole',
            constraint=models.UniqueConstraint(fields=('partner', 'partneruser', 'permission'), name='uniq_partneruserrole'),
        ),
    ]

制約 検索

1
2
3
4
5
6
7
8
9

SELECT DISTINCT A.CONSTRAINT_NAME, A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as A
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE
    A.TABLE_NAME = 'partners_partneruserrole'
    AND B.CONSTRAINT_NAME  like 'uniq_%'
    AND A.CONSTRAINT_NAME  not like 'uniq_%'
    AND A.CONSTRAINT_NAME != 'PRIMARY';

+-----------------------------------------------------------+--------------------------+----------------+----------------------+
| CONSTRAINT_NAME                                           | TABLE_NAME               | COLUMN_NAME    | CONSTRAINT_NAME      |
+-----------------------------------------------------------+--------------------------+----------------+----------------------+
| partners_partneruser_partneruser_id_d59f67cc_fk_partners_ | partners_partneruserrole | partneruser_id | uniq_partneruserrole |
| partners_partneruser_partner_id_852ab185_fk_partners_     | partners_partneruserrole | partner_id     | uniq_partneruserrole |
| partners_partneruser_permission_id_031e3996_fk_auth_perm  | partners_partneruserrole | permission_id  | uniq_partneruserrole |
+-----------------------------------------------------------+--------------------------+----------------+----------------------+

再設定

1
2
3
4
5
6
7

ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partneruser_id_d59f67cc_fk_partners_`;
ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partner_id_852ab185_fk_partners_`;
ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partner_id_852ab185_fk_partners_`, DROP INDEX `uniq_profit`;

ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` FOREIGN KEY (`partneruser_id`) REFERENCES `partners_partneruser` (`id`);
ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_partner_id_852ab185_fk_partners_` FOREIGN KEY (`partner_id`) REFERENCES `partners_partner` (`id`);
ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_permission_id_031e3996_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`user_ptr_id`);

最終スキーマ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DROP TABLE IF EXISTS `partners_partneruserrole`;
CREATE TABLE `partners_partneruserrole` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_partneruserrole` (`partner_id`,`partneruser_id`,`permission_id`),
  KEY `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` (`partneruser_id`),
  KEY `partners_partneruser_permission_id_031e3996_fk_auth_perm` (`permission_id`),
  CONSTRAINT `partners_partneruser_partner_id_852ab185_fk_partners_` FOREIGN KEY (`partner_id`) REFERENCES `partners_partner` (`id`),
  CONSTRAINT `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` FOREIGN KEY (`partneruser_id`) REFERENCES `partners_partneruser` (`user_ptr_id`),
  CONSTRAINT `partners_partneruser_permission_id_031e3996_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;