Creating and Destroying Tables (Programming the Perl DBI)

3.5. Creating and Destroying Tables

The previous section discusses the operations SQL can perform to manipulate data stored as rows within tables in the database. However, there is a separate set of statements that covers the manipulation of the tables (and other objects) within the database themselves. These statements are known as Data Definition Language commands, or DDLs.

The operations that can be performed on tables are fairly basic, since they are quite far-reaching in their consequences. The two simplest operations available are:

Creating a new table

This is done via the CREATE TABLE command, the syntax of which varies depending on the database platform being used. However, this statement generally specifies the name of the table to be created and the definition of all the columns of the table (both names and datatypes).

For example, the SQL we used to create the megaliths table within our database was:

CREATE TABLE megaliths (
    id              INTEGER NOT NULL,
    name            VARCHAR(64),
    location        VARCHAR(64),
    description     VARCHAR(256),
    site_type_id    INTEGER,
    mapref          VARCHAR(16)
)

CREATE TABLE will create a brand-new table with the given definition, which will be completely empty until you insert rows into it.

Deleting, or dropping, an existing table

This action is as drastic as data modification can get. The actual table structure within the database is completely removed, as are any rows of data currently stored within that table. This operation cannot usually be rolled back from. Once the fatal statement is typed, the specified table has gone forever (unless you have made a backup).

The syntax for dropping tables is fairly standard across databases and is extremely straightforward.[33] To completely get rid of our megaliths table, we can issue the SQL statement of:

[33]Something so deadly should have a far more complicated syntax!

DROP TABLE megaliths

There are other ways in which table definitions can be manipulated, and also other database structures that can be created (such as views and indexes). But these are beyond the scope of this book. You should consult your database documentation for more information.