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?
Best Answer
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 asDROP TABLE
. AfterDELETE
, there are no rows in the table, but the table still exists. AfterDROP TABLE
, the table doesn't exist, i.e. it doesn't show up if you runSHOW TABLES
.