How to remove tables and indexes?

Question

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?

Answer 1

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

Answer 2

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.