SQL Pocket Guide, 3rd 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 data types:

  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. Multicolumn constraints must be declared as table-level elements. The examples demonstrate both approaches.

See the platform-specific sections on “Data Types” for lists of valid data types by platform.

Creating a Table: DB2

The following is a typical CREATE TABLE statement for DB2:

CREATE TABLE db2_example ( id DECIMAL(6) NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MAXVALUE 999999 CACHE 20 NO ORDER), name VARCHAR(15) NOT NULL, country VARCHAR(2) DEFAULT 'CA' NOT NULL CONSTRAINT country_check CHECK (country IN ('CA','US')), indexed_name VARCHAR(15), CONSTRAINT db2_example_pk PRIMARY KEY (id), CONSTRAINT db2_example_fk01 FOREIGN KEY (name, country) REFERENCES parent_example (name, country), CONSTRAINT db2_example_u01 UNIQUE (name, country), CONSTRAINT ...