-2

I have a table:

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

I want to remove the UNIQUE KEY unique_style_within_seasons_customer_etc so that I can add another column to it, but when I do it says:

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)

Any ideas on what I've got wrong?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Neil Widdowson
  • 15
  • 6
  • Some other table references your `UNIQUE KEY`. [Here](https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column) on how to find which – markalex Mar 23 '23 at 14:46
  • Worth a read - https://dba.stackexchange.com/questions/102371/how-to-check-foreign-keys-related-to-a-table https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-foreign-table.html – P.Salmon Mar 23 '23 at 14:55
  • @markalex I have already been through that post, but I can't see anywhere that it links to the unique key, nor do I remember ever setting one up to link to do. What exactly would you get me to query to find it becauce I can't work out any links from that thread? – Neil Widdowson Mar 23 '23 at 14:58
  • If you mean that there is a reference to one of the columns used in unique, there is for one table, but that was there before I added the unique key so I'm not sure why that would stop me from deleting the unique. – Neil Widdowson Mar 23 '23 at 15:03

1 Answers1

0

Use this select to find what Foreign key is using your Unique key:

SELECT
  DISTINCT a.TABLE_NAME, b.CONSTRAINT_NAME  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b USING (CONSTRAINT_NAME)
WHERE
    a.TABLE_SCHEMA = '!!!!!YOUR_DB_HERE!!!!!'  AND
    a.REFERENCED_TABLE_NAME = 'styles' AND
    b.UNIQUE_CONSTRAINT_NAME = 'unique_style_within_seasons_customer_etc'
ORDER BY a.TABLE_NAME ASC;
markalex
  • 8,623
  • 2
  • 7
  • 32