Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

How to Drop Some / All Tables


Assuming you have no FOREIGN KEY constraints, you can simply select multiple tables using CUBRID Manager, right click and choose the option Manage Table -> Drop Table.

Using a SQL command, you can type:

DROP TABLE athlete, code, event, game, history, nation, olympic, participant, posts, record, stadium;

You can put any table or tables you want. You only need to separate them with commas and they will be dropped. However, there is one scenario that will lead to the impossibility of running the above mentioned command. If any of the tables have foreign keys referencing other tables without being set to ON DELETE CASCADE (the default is ON DELETE RESTRICT), then the above command will fail.

The solution in this case is to delete all foreign keys before dropping the tables. For example, if you want to drop the game table which has references to athlete and code tables, you must first delete the 2 foreign key constraints. The commands you should run are:

SELECT index_name FROM db_index WHERE class_name = 'game' AND is_foreign_key = 'YES';
ALTER TABLE game DROP FOREIGN KEY fk_game_athlete_code;
ALTER TABLE game DROP FOREIGN KEY fk_game_event_code;

The first command finds the names of the foreign keys that the "game" table has. They are in this case fk_game_athlete_code and fk_game_event_code. Then, simply run the DROP FOREIGN KEY command on each of the two indexes.

This approach has to be made for all the tables that have foreign key constraints without ON DELETE CASCADE. If you encounter an error such as "Error description: The primary key 'pk_athlete_code' referred by a foreign key 'fk_game_athlete_code' is not supposed to be dropped." and you don't know what table contains the fk_game_athlete_code column, check out this tutorial: Dropping Primary Key Error

comments powered by Disqus
Page info
viewed 2476 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article