CODEWITHSUNDEEP
mysqlsqlforeign-keys
Basic
Basic

Reputation: 26766

Unable to DROP a MySQL Table

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?

Upvotes: 3

Views: 6983

Answers (2)

Nathan Q
Nathan Q

Reputation: 1902

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

Upvotes: 8

bcmcfc
bcmcfc

Reputation: 26825

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE Address;
SET FOREIGN_KEY_CHECKS = 1;

Upvotes: 13

Related Questions