MySQL Mysql删除约束

Mysql delete constraint

提问by viv

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.

我需要删除“unique_help”键，但出现外键约束错误。

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

由于这个错误，我无法删除其中的任何内容，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

回答by ypercube??

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

问题是unique_help (section, language)索引被section_constraint外键约束使用。所以你不能在不先删除约束的情况下删除索引。

---

---

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.

然后你可以添加一个（简单的）索引(section)并重新创建外键。

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

在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

在SQL-Fiddle-2测试

回答by Tushar Gupta - curioustushar

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`);

回答by RandomSeed

As your error message suggests:

正如您的错误消息所暗示的那样：

(...) in the [table referenced by a foreign key], there must be an index where the referenced columns are listed as the first columns in the same order.

(...) 在[表引用的外键] 中，必须有一个索引，其中引用的列以相同的顺序列为第一列。

You must find the referencing table(s) and drop the foreign key constraint(s) from this (these) other table(s).

您必须找到引用表并从这个（这些）其他表中删除外键约束。

My bad, I read through your table definition too quickly. The actual problem is the other way around:

我的不好，我太快地通读了你的表定义。实际的问题是相反的：

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

MySQL 需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。

Either drop the section_constraintforeign key constraint first, or create a new index on languagebefore dropping the UNIQUE constraint.

section_constraint首先删除外键约束，或者language在删除 UNIQUE 约束之前创建一个新索引。

回答by Big D.

Here is a stored procedure that you can call to drop a foreign key prior to calling the create sql. I modified an answer to a similar question about DROP PROCEDURE IF EXISTS Answered by: https://stackoverflow.com/users/166161/thomas-paine

这是一个存储过程，您可以在调用 create sql 之前调用它来删除外键。我修改了一个关于 DROP PROCEDURE IF EXISTS 的类似问题的答案 回答者：https: //stackoverflow.com/users/166161/thomas-paine

sample call:

示例调用：

CALL DropForeignKey (DATABASE(), 'tablename', 'keyname');

Procedure:

程序：

DELIMITER ;

/* DROP_FOREIGN_KEY */
DROP PROCEDURE IF EXISTS DropForeignKey;

DELIMITER //

CREATE PROCEDURE DropForeignKey(
IN param_schema VARCHAR(100),
IN param_table_name VARCHAR(100),
IN param_constraint_name VARCHAR(100)
) 

BEGIN
    IF EXISTS(
    SELECT NULL FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME=param_constraint_name AND TABLE_NAME=param_table_name AND TABLE_SCHEMA = param_schema
    )
    THEN
        set @paramTable = param_table_name ;

    set @ParamConstraintName = param_constraint_name ;
    set @ParamSchema = param_schema;
    /* Create the full statement to execute */
    set @StatementToExecute = concat('ALTER TABLE `',@ParamSchema,'`.`',@paramTable,'` DROP FOREIGN KEY `',@ParamConstraintName,'` ');
    /* Prepare and execute the statement that was built */
    prepare DynamicStatement from @StatementToExecute ;
    execute DynamicStatement ;
    /* Cleanup the prepared statement */
    deallocate prepare DynamicStatement ;

END IF;
END //

DELIMITER ;