0

I have a table with a UNIQUE KEY which I need to remove, however I get the following error when I try to:

SQLSTATE[HY000]: General error: 1553 
Cannot drop index 'unique_style_within_seasons_customer_etc': 
needed in a foreign key constraint 
(SQL: ALTER TABLE styles DROP INDEX unique_style_within_seasons_customer_etc)

There are no dependencies on that UNIQUE KEY, only to one of the columns in the table. I am unsure what to do next? The following returns nothing:

SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'knit' AND
    REFERENCED_TABLE_NAME = 'styles' AND
    CONSTRAINT_NAME LIKE '%unique_style_within_seasons_customer_etc%';

The only reference to that table is style_versions on column styles_id, so none of those listed above. Removing the last line returns:

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME REF_TABLE REF_COLUMN
style_versions styles_id style_versions_styles_id_foreign styles id

Show create table output:

CREATE TABLE `styles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `designs_id` bigint unsigned DEFAULT NULL,
  `departments_id` bigint unsigned NOT NULL,
  `seasons_id` bigint unsigned NOT NULL,
  `customers_id` bigint unsigned NOT NULL,
  `customer_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `commodity_codes_id` bigint unsigned DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `last_updated_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `use_version` bigint unsigned DEFAULT NULL,
  `carryover` tinyint(1) DEFAULT NULL,
  `category` enum('mens','ladies','accessories','childrens') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cancelled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_style_within_seasons_customer_etc` (`designs_id`,`departments_id`,`seasons_id`,`customers_id`,`category`),
  KEY `styles_departments_id_foreign` (`departments_id`),
  KEY `styles_seasons_id_foreign` (`seasons_id`),
  KEY `styles_customers_id_foreign` (`customers_id`),
  KEY `styles_commodity_codes_id_foreign` (`commodity_codes_id`),
  KEY `styles_last_updated_by_foreign` (`last_updated_by`),
  KEY `styles_use_version_foreign` (`use_version`),
  CONSTRAINT `styles_commodity_codes_id_foreign` FOREIGN KEY (`commodity_codes_id`) REFERENCES `commodity_codes` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `styles_customers_id_foreign` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `styles_departments_id_foreign` FOREIGN KEY (`departments_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `styles_designs_id_foreign` FOREIGN KEY (`designs_id`) REFERENCES `designs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `styles_last_updated_by_foreign` FOREIGN KEY (`last_updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `styles_seasons_id_foreign` FOREIGN KEY (`seasons_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `styles_use_version_foreign` FOREIGN KEY (`use_version`) REFERENCES `style_versions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
2
  • Try to reproduce your problem on some online fiddle. Copy all CREATE TABLE to the fiddle then ensure that DROP INDEX fails. Then provide the link to the fiddle.
    – Akina
    Mar 24, 2023 at 14:47
  • Look for other tables with FOREIGN KEYs to this column.
    – Rick James
    Apr 6, 2023 at 5:43

1 Answer 1

0

This might work:

ALTER TABLE styles
    ADD INDEX UNIQUE KEY (`designs_id`, `departments_id`,
                  `seasons_id`, `customers_id`, `category`);

ALTER TABLE styles
    DROP INDEX `unique_style_within_seasons_customer_etc`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.