Java Enterprise in a Nutshell, Third Edition

Schema Manipulation Commands

SQL includes two broad classes of commands. The first class is schema manipulation commands , which allow the creation, modification, and deletion of high-level database objects such as tables. This section describes these commands. We’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 uppercase constant width are part of the SQL statement, while items in lowercase constant width italic represent names and values that you supply when using the statement.

CREATE TABLE

As its name says, the CREATE TABLE 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 PRIMARY KEY and NOT NULL identifiers are column constraints. The NOT NULL constraint prevents any entry in a column from being set to null. Here, it’s combined it with a default ...