I am facing a problematic scenario with my database table. I have a database table which is parent and it has got some garbage values. Now, I want to drop this table. Is there any way that I can drop this table and recreate it without affecting its child tables?
Hi there,
If you did not define foreign key constraint then that would not be a problem, you can delete the parent table freely. But if you did, you need to deal with it. In order not to affect the child tables, drop the foreign key constraints first before the parent table.This is the query to drop foreign key constraint: ALTER TABLE <child table name>DROP FOREIGN KEY <constraint key>. To get your constraint key, run this query: SHOW CREATE TABLE <tablename>.
You could also check your dump file, in case you are doing mysqldump. It has the table definitions and the foreign keys. You can recreate the foreign key constraint after the parent table is created again using the alter table query.
Hi,
It’s quite simple. If you haven’t defined the parent child (foreign key) relationship and using logical relations, then you need not to worry and go ahead straight away to drop the table. But if you have defined the relationships, then you have to take care of the child tables. The first thing you need to do is drop the foreign key constraint defined with this table by using query: ALTER TABLE <child Table name> DROP FOREIGN KEY <constraint name>.
Once your foreign key constraints are dropped, you are ready to drop the parent table. Now, after creating your parent table again, you can recreate the foreign key constraint with child tables to bring it back to previous state.
Â
