I have one parent table called Parent. the id of parent table of used as a foreign key for other 3 tables child1,child2,child3
I want to drop the table and create the parent table again. I don't want to lose data in the child tables.
-
2Are you using MySQL or Postgresql?jarlh– jarlh2018-09-26 14:08:57 +00:00Commented Sep 26, 2018 at 14:08
-
@jarlh i assume its postgresql as it has two tags and is in the titleWhatsThePoint– WhatsThePoint2018-09-26 14:20:43 +00:00Commented Sep 26, 2018 at 14:20
Add a comment
|
2 Answers
Reminder, caution!
Before use, make sure to try this out in an explicit transaction to check if the statement does exactly what you wanted it to do and nothing more. You can start a transaction using BEGIN; and save it using COMMIT;. In case anything goes wrong, you can always roll it back manually using ROLLBACK;
Related docs: transactions
Solution/1
Using DROP TABLE ... CASCADE will remove the table and ALL objects that depend on it (views, foreign key constraints ...), listing them as a NOTICE in the output (at least in psql):
-- Replace table_name with the name of your parent table
DROP TABLE table_name CASCADE;
Quoting the manual here, bold emphasis mine:
(...) to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)
Solution/2
If you prefer to do things manually (when you are not aware of the scope of dependencies to your table), drop the constraints you have on your child tables and remove the parent table as usually.
Typically, you would perform these operations:
-- Replace object names to suit your case
-- Dropping foreign key constraint on child table pointing to parent table
ALTER TABLE child_table DROP CONSTRAINT constraint_name;
-- Repeat above for all constraints in all child tables
-- Drop the parent table
DROP TABLE parent_table;
Related docs: dropping constraint
4 Comments
Raymond Nijland
Be carefull
DROP TABLE parent_table CASCADE; drops ALL objects related to that table also meaning triggers and views.
Kamil Gosciminski
@wildplasser how does CASCADE removes data from other tables?
Raymond Nijland
".... I don't want to lose data of child table." @wildplasser " but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely"
Kamil Gosciminski
@RaymondNijland I've bolded the ALL word, thanks for addition.
You must drop all foreign keys from your parent table to childs. i.e. drop the foreign key of child1 with:
ALTER TABLE parent_table
DROP CONSTRAINT IF EXISTS fk_to_child1;
Later DROP the table then when you create again the table don't forget recreate the foreign keys.
answered Sep 26, 2018 at 14:10
Ernesto Campohermoso
7,4311 gold badge42 silver badges51 bronze badges