Discovering SQL: A Hands-On Guide for Beginners

REFACTORING DATABASE TABLE

Now that you know about data types, you need to bring your tables up to standard by identifying which columns can be served better with which data type. The refactoring table does not equal refactoring database design (this will be the subject of the next chapter), but it will prepare you to take this step. All the statements used in this section are DDL statements.

How do you alter the past? One way is to drop everything and start anew. The DML statement DROP will serve the purpose.

DROP TABLE

Once dropped, the table can't be restored unless you were careful enough to drop it as part of the transaction (see Chapter 10 for details on transactional support). Not every RDBMS has transactional support for DDL statements. Microsoft SQL Server, Oracle, and IBM DB2 have it; MySQL and PostgreSQL don't. Neither Microsoft Access nor HSQLDB embedded into OpenOffice BASE has it.

Because a table occupies physical space, it is prudent to remove it. The DROP TABLE statement removes logical objects associated with it, such as INDEX (see Chapter 9 for more details on indices), constraints, and triggers (see Chapter 4). The syntax is virtually identical across all RDBMSs:

DROP TABLE <table_name>;

Sometimes you need to use a fully qualified name, including the table's schema, and you need to have the privileges assigned to you as a user to do so (see Chapter 10 for more information on database privileges).

If a table has referential constraints (explained in detail in ...