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