无法删除MySQL表 [英] Unable to DROP a MySQL Table

问题描述

表的定义是noddy：

（
`Id` int（11）NOT NULL AUTO_INCREMENT，
`ContactId` int（11）NOT NULL，

  CREATE TABLE IF NOT EXISTS 
 PRIMARY KEY（`Id`），
 KEY`ContactId`（`ContactId`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; 
  

这导致

＃1217 - 无法删除或更新父行：外键约束失败

ContactId有一个约束，已删除它。

PHPMyAdmin的导出函数不显示任何超出上面显示的表定义。表中没有行，据我所知，没有FK引用 Address.Id 字段（但我不知道如何验证）。 >

可以有人请告诉我如何摆脱表？

解决方案

要列出外键

  select 
 concat（table_name，'。'，column_name）as'foreign key'，
 concat（referenced_table_name，'。'，referenced_column_name）as'references'
 from 
 information_schema.key_column_usage 
其中
 referenced_table_name不为空; 
  

对于您的案例中的特定搜索：

<$从
中选择
constraint_name
information_schema.key_column_usage
其中
referenced_table_name ='地址'AND referenced_column_name ='ContactId';

删除外键约束：

  ALTER TABLE [table_name] DROP FOREIGN KEY [constraint_name]; 
  

I need to drop a deprecated, empty table from my MySQL Database.

The table definition is noddy:

CREATE TABLE IF NOT EXISTS `Address` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ContactId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `ContactId` (`ContactId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

This results in

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

There was a constraint on ContactId but II've removed it.

PHPMyAdmin's export function doesn't show anything beyond the table definition shown above. There are no rows in the table and, to my knowledge, no FKs reference the Address.Id field (But I don't know how to verify this).

Can someone please advise how I can get rid of the table?

解决方案

To list foreign keys

select 
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

For specifc search in your case:

select 
    constraint_name
from
    information_schema.key_column_usage
where
    referenced_table_name = 'Address' AND referenced_column_name = 'ContactId';

To drop a foreign key constraint:

ALTER TABLE [table_name] DROP FOREIGN KEY [constraint_name];

这篇关于无法删除MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！