Programming the Perl DBI

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 ...