Schema Manipulation Commands (Java Enterprise in a Nutshell)

8.3. Schema Manipulation Commands

SQL includes two broad classes of commands. The first are schema manipulation commands, which allow the creation, modification, and deletion of high-level database objects such as tables. This section describes these commands. I've provided a syntax summary for each command. In case you aren't familiar with the style, items in square brackets are optional or not always required, while items in curly braces are either always required or required within the context of some optional item. A vertical bar (|) indicates a choice, while an ellipsis indicates that an entry may be repeated any number of times. Items in all uppercase are part of the SQL statement, while items in lowercase constant width italic represent names and values that you supply when using the statement.

8.3.1. CREATE TABLE

As its name says, the CREATETABLE command creates a table. Here's the syntax:

CREATE [ [ GLOBAL | LOCAL ] TEMPORARY ] TABLE table_name
 ( { column_name { data_type | domain_name } [ column_size ] 
      [ column_constraint ... ] ... } 
    [ DEFAULT default_value ], ...
    [ table_constraint ], ...
    [ ON COMMIT { DELETE | PRESERVE } ROWS ] )

Here's a simple example:

CREATE TABLE BOOKS
 ( 
   TITLE VARCHAR (25) PRIMARY KEY,
   AUTHOR VARCHAR(25) NOT NULL DEFAULT `Unknown',
   EDITION INTEGER,
   PRICE NUMBER(6,2)
 )

The PRIMARYKEY and NOTNULL identifiers are column constraints. The NOTNULL constraint prevents any entry in a column being set to null. Here, it's combined it with a default value. PRIMARYKEY identifies the column that's used as the primary key (or main unique identifier) for the table. If a table has a primary key column (it doesn't have to), there can be only one such column, no row has a null value in the primary key column, and no two rows have the same primary key.

A table constraint affects every row in the table. UNIQUE is a common example:

CREATE TABLE BOOKS
 ( 
   TITLE VARCHAR (25),
   AUTHOR VARCHAR(25),
   EDITION INTEGER,
   PRICE NUMBER(6,2),
   UNIQUE
 )

Used as a table constraint, UNIQUE indicates that each row in the table must have a unique combination of values. You can also specify particular columns that must form a unique combination:

UNIQUE(TITLE, AUTHOR, EDITION)

This mandates only unique title/author/edition combinations. Note that UNIQUE can also be used as a column constraint.

We can use PRIMARYKEY as a table constraint to specify more than one column as the primary key:

CREATE TABLE BOOKS
 ( 
   TITLE VARCHAR (25) NOT NULL,
   AUTHOR VARCHAR(25) NOT NULL,
   EDITION INTEGER NOT NULL,
   PRICE NUMBER(6,2),
   PRIMARY KEY (TITLE, AUTHOR, EDITION)
 )

Since entry-level SQL-92 requires that primary keys remain not null, we use NOTNULL column constraints on the primary key columns in this case.

8.3.2. ALTER TABLE

The ALTERTABLE command allows you to modify the structure of an existing table. Here's the syntax:

ALTER TABLE table_name
   { ADD [COLUMN] column_name definition }
   { ALTER [COLUMN] column_name definition
      { SET DEFAULT default_value } | { DROP DEFAULT } }
   { DROP [COLUMN] COLUMN_NAME [ RESTRICT | CASCADE ] }
   { ADD table_constraint_definition }
   { DROP constraint_name  [ RESTRICT | CASCADE] }

Note that the modifications you can make are somewhat limited. While you can add and remove columns (subject to the requirements of any constraints that may have been placed on the table), you cannot reorder columns. To perform major changes, you generally need to create a new table and move the existing data from the old table to the new table.

Here's a statement that adds two columns to a table:

ALTER TABLE BOOKS
 ADD PUBLISHED_DATE DATE,
 ADD PUBLISHER CHAR (30) NOT NULL

Note that the ability to specify multiple operations in an ALTER TABLE command is not part of the SQL specification, although most databases support this functionality.

Here's how to change the type of a column:[3]

[3] If you look back at the syntax for ALTER TABLE, you'll see that the official syntax for this kind of operation is ALTER, although most databases use MODIFY instead.

ALTER TABLE BOOKS
 MODIFY PUBLISHER VARCHAR (25)

When this statement runs, the database attempts to convert all existing data into the new format. If this is impossible, the modification fails. In the previous example, if any record has a publisher entry of more than 30 characters, the statement might fail (exact behavior depends on the implementation). If you are converting from a character field to, say, an integer field, the whole ALTER TABLE command might fail entirely. At the minimum, such a change requires that all entries contain a valid string representation of an integer.

To allow null values in the PUBLISHER column, use MODIFY:

ALTER TABLE BOOKS
 MODIFY PUBLISHER NULL

To remove the PUBLISHER column entirely, use DROP:

ALTER TABLE BOOKS
 DROP PUBLISHER

The ALTERTABLE command is not required for entry-level SQL-92 conformance. Due to its extreme usefulness, however, it is supported by most DBMS packages, although it often varies from the standard. More esoteric features, such as the RENAME command, are not supported by most packages. In general, it is not safe to count on anything beyond the basic ADD, DROP, and MODIFY (ALTER) commands.

8.3.3. DROP

The DROP command allows you to permanently delete an object within the database. For example, to drop the BOOKS table, execute this statement:

DROP TABLE BOOKS

DROP also can delete other database objects, such as indexes, views, and domains:

DROP INDEX index_name
DROP VIEW view_name
DROP DOMAIN domain_name

Once something has been dropped, it is usually gone for good--certainly once the current transaction has been committed, but often before.