Mysql报错1091解决办法,即使无法存在，也无法删除密钥：错误代码：1091

I have a table which looks like this:

CREATE TABLE `articles`

(

`article_id` INT(10) NOT NULL auto_increment,

`pubsrc_id` INT(10) NOT NULL,

`pub_id` VARCHAR(16) NOT NULL

PRIMARY KEY (`article_id`),

UNIQUE KEY `pubsrc_id` (`pubsrc_id`,`pub_id`),

CONSTRAINT `pub_articles_ibfk_1` FOREIGN KEY (`pubsrc_id`) REFERENCES `pub_sources` (`pubsrc_id`) ON

DELETE RESTRICT

ON

UPDATE RESTRICT,

CONSTRAINT `pub_articles_ibfk_2` FOREIGN KEY (`pubsrc_id`) REFERENCES `pub_sources` (`pubsrc_id`)

)

There is another table which the previous table has a key to:

CREATE TABLE `pub_sources`

(

`pubsrc_id` INT(11) NOT NULL,

`pubsrc_name` VARCHAR(32) NOT NULL

PRIMARY KEY (`pubsrc_id`)

)

I am trying to drop the foregin key with the command:

SET SESSION SQL_SAFE_UPDATES = 0;

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `articles` DROP FOREIGN KEY `pubsrc_id`;

but get the following error:

Error Code: 1091. Can't DROP 'pubsrc_id'; check that column/key exists

解决方案

You need to drop a foreign key by its constraint name, not the name of the column it's defined on.

Not okay:

mysql> ALTER TABLE `articles` DROP FOREIGN KEY `pubsrc_id`;

ERROR 1091 (42000): Can't DROP 'pubsrc_id'; check that column/key exists

Okay:

mysql> ALTER TABLE `articles` DROP FOREIGN KEY `pub_articles_ibfk_1`;

Query OK, 0 rows affected (0.03 sec)