Mysql delete constraint

Question

I have a table with below structure :

    CREATE TABLE `Lm_help` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `section` int(10) NOT NULL,
 `language` int(10) NOT NULL,
 `title` varchar(255) NOT NULL,
 `text` text NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_help` (`section`,`language`),
 KEY `language_constraint` (`language`),
 CONSTRAINT `language_constraint` FOREIGN KEY (`language`) REFERENCES `Lm_languages` (`id`),
 CONSTRAINT `section_constraint` FOREIGN KEY (`section`) REFERENCES `Lm_help_sections` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

I need to remove "unique_help" key, but I am getting foreign key constraint error.

Due to this error I not able to remove anything among these, section_constraint, language_constraint, unique_help.

Below are other tables that refer to this :

CREATE TABLE `Lm_languages` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `code` varchar(255) NOT NULL,
 `status` int(11) DEFAULT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1




CREATE TABLE `Lm_help_sections` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Answer 1

The problem is that the unique_help (section, language) index is used by the section_constraint foreign key constraint. So you can't drop the index without dropping the constraint first.

---

One way to solve this, is to drop the foreign key constraint first, then drop the index.

Then you can add a (simple) index on (section) and recreate the foreign key.

All these can be done in one statement:

ALTER TABLE Lm_help
    DROP FOREIGN KEY section_constraint,   -- drop the FK so
    DROP INDEX unique_help,                -- the index can be dropped
                                           -- and then
    ADD INDEX section_IX (section),        -- add a new index
    ADD CONSTRAINT section_FK              -- so the FK can be recreated
        FOREIGN KEY (section) 
        REFERENCES Lm_help_sections (id)
  ;

Tested at SQL-Fiddle

---

Improvement

I was wrong, no need to drop and recreate the constraint. The index can be dropped, as long a new index is created:

ALTER TABLE Lm_help
    DROP INDEX unique_help,
    ADD INDEX section_IX (section) 
  ; 

Tested at SQL-Fiddle-2

Answer 2

ALTER TABLE Orders
DROP FOREIGN KEY 'language_constraint';

ALTER TABLE Orders
DROP FOREIGN KEY 'section_constraint';

PERFORM DELETE QUERY HERE

ALTER TABLE Orders
ADD CONSTRAINT `language_constraint`
FOREIGN KEY (`language`)
REFERENCES `Lm_languages` (`id`);

ALTER TABLE Orders
ADD CONSTRAINT `section_constraint`
FOREIGN KEY (`section`)
REFERENCES `Lm_help_sections` (`id`);