SQL Pocket Guide, 2nd Edition

Tables, Creating

You create a new table in a database by issuing a CREATE TABLE statement. The syntax varies widely among vendors, but the following subsections show reasonable examples for each platform. Bear in mind the following points:

• At a minimum, all you need is a list of column names and their datatypes:

  CREATE TABLE simple_example (
     id NUMERIC,
     name VARCHAR(15),
     last_changed DATE
  );

• The examples give explicit names for many of the constraints, which I consider a best practice, but the CONSTRAINT constraint_name syntax is optional and is often omitted (especially on column constraints such as the NOT NULL constraint).

• You can usually declare constraints that involve a single column as part of that column’s definition. Multi-column constraints must be declared as table-level elements. The examples demonstrate both approaches.

See the "Datatypes" section, earlier in this book, for a list of valid datatypes by platform.

Creating a Table (Oracle)

The following is a typical CREATE TABLE statement for Oracle:

CREATE TABLE oracle_example ( id NUMBER(6), name VARCHAR2(15) NOT NULL, country VARCHAR2(2) DEFAULT 'CA' CONSTRAINT country_not_null NOT NULL CONSTRAINT country_check CHECK (country IN ('CA','US')), indexed_name VARCHAR2(15), CONSTRAINT oracle_example_pk PRIMARY KEY (id), CONSTRAINT oracle_example_fk01 FOREIGN KEY (name, country) REFERENCES parent_example (name, country), CONSTRAINT oracle_example_u01 UNIQUE (name, country), CONSTRAINT oracle_example_index_upper CHECK (indexed_name ...