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

Get SQL Pocket Guide, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.