Remove zombie foreign keys in MYSQL
Posted by: Harold Ship
Date: February 20, 2020 10:49PM

I'm trying to build a MySQL database gradually, by generation using JPA and Eclipse-Link. Along the way, I've changed some relationships `@ManyToOne`, `@OneToOne` etc.

I now have a situation where I have some spurious foreign keys: the tables don't exist, but the referenced tables still do. I think the original tables were cross-reference tables generated by EclipseLink but are no longer around.

The issue is, I cannot delete these referenced tables. I get an error like this:

mysql> drop table PRODUCTDO;
ERROR 3730 (HY000): Cannot drop table 'PRODUCTDO' referenced by a foreign key constraint 'PRODUCTDO_DISTRIBUTIONCENTERPRODUCTDO_ProductDo_ID' on table 'PRODUCTDO_DISTRIBUTIONCENTERPRODUCTDO'.


If I run:

SET foreign_key_checks = 0;


then I can delete the table, but the constraint still remains. Even if I drop the database and create it again, the constraint is still there:


mysql> SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'SCO';
+---------------------------------------+------------------------------+----------------------------------------------------+-----------------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------------------------+------------------------------+----------------------------------------------------+-----------------------------+------------------------+
| PRODUCTDO_DISTRIBUTIONCENTERPRODUCTDO | ProductDo_ID | PRODUCTDO_DISTRIBUTIONCENTERPRODUCTDO_ProductDo_ID | PRODUCTDO | ID |
| PRODUCTDO_DISTRIBUTIONCENTERPRODUCTDO | distributionCenterProduct_ID | PRDCTDDSTRBTIONCENTERPRODUCTDOdstrbtnCntrProductID | DISTRIBUTIONCENTERPRODUCTDO | ID |
+---------------------------------------+------------------------------+----------------------------------------------------+-----------------------------+------------------------+
2 rows in set (0.01 sec)


**How can I get rid of these zombie constraints?**

Options: ReplyQuote


Subject
Views
Written By
Posted
Remove zombie foreign keys in MYSQL
256
February 20, 2020 10:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.