A CREATE TABLE statement creates a table. Tables contain columns
and constraints, rules to which data must conform. Table-level constraints
specify a column or columns. Columns have a data type and can specify column
constraints (column-level constraints).
The table owner and the
database owner
automatically gain the following privileges on the table and are able
to grant these privileges to other users:
- INSERT
- SELECT
- REFERENCES
- TRIGGER
- UPDATE
These privileges cannot be revoked from the table and database owners.
For
information about constraints, see CONSTRAINT clause.
You
can specify a default value for a column. A default value is the value to
be inserted into a column if no other value is specified. If not explicitly
specified, the default value of a column is NULL. See Column default.
You
can specify storage properties such as page size for a table by calling the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system
procedure.
If a qualified table name is specified, the schema name cannot
begin with SYS.
Example
CREATE TABLE HOTELAVAILABILITY
(HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
-- the table-level primary key definition allows you to
-- include two columns in the primary key definition
PRIMARY KEY (hotel_id, booking_date))
-- assign an identity column attribute to an INTEGER
-- column, and also define a primary key constraint
-- on the column
CREATE TABLE PEOPLE
(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26));
-- assign an identity column attribute to a SMALLINT
-- column with an initial value of 5 and an increment value
-- of 5.
CREATE TABLE GROUPS
(GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));
Note: For
more examples of CREATE TABLE statements using the various constraints, see
CONSTRAINT clause.
CREATE TABLE ... AS ...
With the alternate form of the CREATE TABLE statement, the column
names and/or the column data types can be specified by providing
a query. The columns in the query result are used as a model for
creating the columns in the new table.
If no column names are specified for the new table, then all the columns
in the result of the query expression are used to create same-named
columns in the new table, of the corresponding data type(s).
If one or more column names are specified
for the new table, then the same number of columns must be present in
the result of the query expression; the data types of those columns are
used for the corresponding columns of the new table.
The WITH NO DATA clause specifies that the data rows which result
from evaluating the query expression are not used; only the names and
data types of the columns in the query result are used. The
WITH NO DATA clause must be specified; in a future release,
Derby may be modified to allow the WITH DATA clause to be provided,
which would indicate that the results of the query expression should
be inserted into the newly-created table. In the current release, however,
only the WITH NO DATA form of the statement is accepted.
Example
-- create a new table using all the columns and data types
-- from an existing table:
CREATE TABLE T3 AS SELECT * FROM T1 WITH NO DATA;
-- create a new table, providing new names for the columns, but
-- using the data types from the columns of an existing table:
CREATE TABLE T3 (A,B,C,D,E) AS SELECT * FROM T1 WITH NO DATA;
-- create a new table, providing new names for the columns,
-- using the data types from the indicated columns of an existing table:
CREATE TABLE T3 (A,B,C) AS SELECT V,DP,I FROM T1 WITH NO DATA;
-- This example shows that the columns in the result of the
-- query expression may be unnamed expressions, but their data
-- types can still be used to provide the data types for the
-- corresponding named columns in the newly-created table:
CREATE TABLE T3 (X,Y) AS SELECT 2*I,2.0*F FROM T1 WITH NO DATA;
