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?