Deleting parent table without affecting child tables?

Asked By nadeemrao 0 points N/A Posted on - 08/09/2011

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?

• Status: Open
• Question Views: 2170
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by dainy

Go To Solution

Answered By Wanda Garcia 0 points N/A #92751

Deleting parent table without affecting 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.

About Wanda Garcia

Questions
0

Answers
6

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/11/2011
• Question Category: MS SQL Server

Best Answer

Best Answer

Answered By dainy 0 points N/A #92752

Deleting parent table without affecting child tables?

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.

 

About dainy

Questions
0

Answers
5

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/19/2011
• Question Category: MS SQL Server