即使存在也无法删除密钥:错误代码:1091

2022-01-20 00:00:00 foreign-keys mysql

我有一个如下所示的表格:

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

但得到以下错误:

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.

不好:

mysql> ALTER TABLE `articles` DROP FOREIGN KEY `pubsrc_id`;
ERROR 1091 (42000): Can't DROP 'pubsrc_id'; check that column/key exists

好的:

mysql> ALTER TABLE `articles` DROP FOREIGN KEY `pub_articles_ibfk_1`;
Query OK, 0 rows affected (0.03 sec)

相关文章