Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why would dropping an empty table produce a foreign key error?

In MySQL, I have a table with the following definition

CREATE TABLE `enterprise_rma` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'RMA Id',
  `status` varchar(32) DEFAULT NULL COMMENT 'Status',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
  `increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
  `date_requested` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'RMA Requested At',
  `order_id` int(10) unsigned NOT NULL COMMENT 'Order Id',
  `order_increment_id` varchar(50) DEFAULT NULL COMMENT 'Order Increment Id',
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
  `customer_id` int(10) unsigned DEFAULT NULL,
  `customer_custom_email` varchar(255) DEFAULT NULL COMMENT 'Customer Custom Email',
  PRIMARY KEY (`entity_id`),
  KEY `IDX_ENTERPRISE_RMA_STATUS` (`status`),
  KEY `IDX_ENTERPRISE_RMA_IS_ACTIVE` (`is_active`),
  KEY `IDX_ENTERPRISE_RMA_INCREMENT_ID` (`increment_id`),
  KEY `IDX_ENTERPRISE_RMA_DATE_REQUESTED` (`date_requested`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_ID` (`order_id`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_INCREMENT_ID` (`order_increment_id`),
  KEY `IDX_ENTERPRISE_RMA_STORE_ID` (`store_id`),
  KEY `IDX_ENTERPRISE_RMA_CUSTOMER_ID` (`customer_id`),
  CONSTRAINT `FK_ENTERPRISE_RMA_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_ENTERPRISE_RMA_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='RMA LIst';

I'm trying to DROP this table, but whenever I do I receive the following error.

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

What I don't understand is, the table is already empty. If I SELECT * FROM enterprise_rma;, no rows are returned (0 rows affected)

enter image description here

and SHOW ENGINE INNODB STATUS; returns nothing useful.

I know I can work around this with the following

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE enterprise_rma;
SET FOREIGN_KEY_CHECKS=0; 

But I don't understand how a foreign key constraint could fail if there's no data in the table. Is there a subtlety of MySQL's foreign key mode I don't understand? Can MySQL get damaged or put in an invalid state when this happens? Are there methods for troubleshooting this myself?

enter image description here

share|improve this question

1 Answer 1

up vote 4 down vote accepted

You shouldn't be able to drop a table that is the parent of another dependent table, even if it has no data. Because if you drop the parent, then you will never be able to insert any data in the child. You must first drop any child tables that reference enterprise_rma.

Something doesn't match your description, though. You said you were having trouble dropping the table, but your example about the foreign key checks shows you using DELETE.

DELETE is not the same as DROP TABLE. After DELETE, there are no rows in the table, but the table still exists. After DROP TABLE, the table doesn't exist, i.e. it doesn't show up if you run SHOW TABLES.

share|improve this answer
    
Thanks Bill, derpy morning. This particular database has had a lot of other integrity issues so I assumed it must have been something like that. –  Alan Storm Jun 18 at 20:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.