MySQL Foreign Key Prevents Deleting Table — But Table is Empty

Question

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)

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?

Answer 1

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.