无法删除mysql,无法删除MySQL表

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值