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