Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed.

How to remove table and indexes from a database? Is there a generic way to remove them in MySQL and PostgreSQL?

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

The generic way to "remove" a table is to drop it:

DROP TABLE foo;

When you drop the table all indexes will also be dropped. If you need to drop them separately use

DROP INDEX foo_index;

For PostgreSQL the easiest way to get rid of all objects in a schema is to drop the schema:

DROP SCHEMA test_schema CASCADE;

Of course you could have found out that yourself by simply looking at the manual:

http://www.postgresql.org/docs/current/static/sql-droptable.html
http://www.postgresql.org/docs/current/static/sql-createindex.html

http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
http://dev.mysql.com/doc/refman/5.5/en/drop-index.html

share|improve this answer
add comment

As far as I know there is no generic way to so except creating another file that contains series of appropriate DROP statements.

Another way which I believe is more appropriate for testing is to create a backup, run your tests, and then restore database from the backup. Some RDMS (not Mysql though, and I'm not sure about Postgres) let you create database snapshots and restore from them later which is much faster then backup-restore.

share|improve this answer
3  
Good point about the test initialization. In that case using create database .. with template bar; will be a lot more efficient in Postgres than restoring a dump. –  a_horse_with_no_name Oct 28 '12 at 13:24
    
@a_horse_with_no_name : Thanks, it's good to know about Postgres with template –  a1ex07 Oct 28 '12 at 13:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.