0

I have the following table:

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `fullname` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `active` tinyint(1) DEFAULT 0,
  `activation_token` varchar(60) DEFAULT NULL,
  `token_expiration` timestamp NULL DEFAULT NULL,
  `role` enum('EMPLOYEE','MANAGER') NOT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_timestamp` timestamp NULL DEFAULT current_timestamp(),
  `last_update_timestamp` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vaccations` (
  `vaccation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `from` date NOT NULL,
  `until` date NOT NULL,
  `approve` tinyint(1) NOT NULL DEFAULT 0,
  `comments` text DEFAULT NULL,
  `request_timestamp` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`vaccation_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `vaccations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `date_range_valid` CHECK (`from` <= `until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And I created the following index:

CREATE UNIQUE INDEX IF NOT EXISTS no_duplicate_vaccations ON vaccations(`user_id`,`from`,aproval_status);

But once I try to drop it:

DROP INDEX IF EXISTS no_duplicate_vaccations on vaccations;

How I can drop it in a manner that I will not lode data due to cascading?

1
  • Did you get an error message from the DROP? What do you mean by "lode data"?
    – Rick James
    Commented Jun 17, 2023 at 14:51

1 Answer 1

0

You'll need to drop and re-create the fopreign key index:

ALTER TABLE vaccations DROP FOREIGN KEY IF EXISTS vaccations_ibfk_1 ;
DROP INDEX IF EXISTS no_duplicate_vaccations on vaccations;
ALTER TABLE vaccations ADD CONTRAINT vaccations_ibfk_1 FOREIGN KEY user_id REFERENCES users(user_id) ;

In order to ensure that theese commands will run in one-go run it upon a transaction:

BEGIN TRANSACTION;

ALTER TABLE vaccations DROP FOREIGN KEY IF EXISTS vaccations_ibfk_1 ;
DROP INDEX IF EXISTS no_duplicate_vaccations on vaccations;
ALTER TABLE vaccations ADD CONTRAINT vaccations_ibfk_1 FOREIGN KEY user_id REFERENCES users(user_id) ;

COMMIT;
1
  • 1
    Older versions of MySQL do not honor BEGIN+COMMIT for wrapping DDL statements. Instead, combine the 3 ALTERs into a single ALTER statement.
    – Rick James
    Commented Jun 17, 2023 at 14:50

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.