mysql - 当 Key_name 与 Column_name 不匹配时，删除外键不起作用

Question

当我运行时:

ALTER TABLE `example` DROP FOREIGN KEY `example_configs_user_email_foreign`;

它运行时没有错误，但是外键没有被删除。当我查看表索引时，它仍然显示:

Key_name: example_configs_user_email_foreign
Column_name: user_email
Non_unique: 1

我的 table :

CREATE TABLE `example` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url_slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `example_configs_url_slug_unique` (`url_slug`),
  KEY `example_configs_user_email_foreign` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我错过了什么？我需要做什么才能删除 FK。

Answer 1

create schema test8484a;
use test8484a;

create table sometable
(   id int auto_increment primary key,
    user_email varchar(255) not null COLLATE utf8_unicode_ci NOT NULL,
    key `keyname8282`(user_email)
)engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `example` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url_slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `example_configs_url_slug_unique` (`url_slug`),
  CONSTRAINT `example_configs_user_email_foreign` 
      FOREIGN KEY (`user_email`) 
      REFERENCES sometable(`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

show create table example;
CREATE TABLE `example` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `url_slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `user_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `example_configs_url_slug_unique` (`url_slug`),
   KEY `example_configs_user_email_foreign` (`user_email`), -- created on your behalf
   CONSTRAINT `example_configs_user_email_foreign` FOREIGN KEY (`user_email`) REFERENCES `sometable` (`user_email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- find the FK name above in case some name mangling occurred

alter table example drop foreign key example_configs_user_email_foreign;
show create table example;
CREATE TABLE `example` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `url_slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `user_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `example_configs_url_slug_unique` (`url_slug`),
   KEY `example_configs_user_email_foreign` (`user_email`) -- residue remains ***********
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

-- cleanup:
drop schema test8484a;

使用 show create table theTableName 检查保留的名称和索引。请注意“drop FK”后保留的残留 Helper 索引。

最初创建 FK 时，会创建子表中的 Helper 索引。下跌后FK该指数仍然存在。

来自标题为 Using FOREIGN KEY Constraints 的手册页:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.