CREATE TABLE

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can also include other attributes of the table, such as its primary key.

Invocation

This statement can be used in an application program using the DB2 CLI functions or issued through the CLP.

Syntax 

>>-CREATE TABLE--table-name--| element-list |--+-----------------+-><
                                               '-WITH ENCRYPTION-'
 
element-list:
 
      .-,--------------------------------------------------.
      V                                                    |
|--(----+-column-name--| data-type |--| column-options |-+-+--)--|
        |                 .-,-----------.                |
        |                 V             |                |
        +-PRIMARY KEY--(----column-name-+--)-------------+
        +-| referential-constraint |---------------------+
        '-CHECK--(--check-condition--)-------------------'
 
data-type:
 
|--+-+-INTEGER-+------------------------+-----------------------|
   | '-INT-----'                        |
   +-SMALLINT---------------------------+
   +-DECIMAL--(--integer--,--integer--)-+
   +-CHAR--(--integer--)----------------+
   +-CHARACTER--(--integer--)-----------+
   +-VARCHAR--(--integer--)-------------+
   +-BLOB--(--integer--)----------------+
   +-DATE-------------------------------+
   +-TIME-------------------------------+
   '-TIMESTAMP--------------------------'
 
column-options:
 
   .--------------------------------------------.
   V                                            |
|----+----------------------------------------+-+---------------|
     +-NOT NULL-------------------------------+
     +-+-PRIMARY KEY------------------+-------+
     | +-REFERENCES--table-name-------+       |
     | '-CHECK--(--check-condition--)-'       |
     +-DEFAULT--+-constant------------------+-+
     |          '-datetime-special-register-' |
     '-GENERATED ALWAYS AS IDENTITY-----------'
 
referential-constraint:
 
                   .-,-----------.
                   V             |
|--FOREIGN KEY--(----column-name-+--)--REFERENCES--table-name---|

Description

table-name
Names the table. The name can be up to 18 bytes long. The name must not identify a table in the catalog. The name must be unique for the mobile device.

Table names are converted to uppercase before being stored in the catalog. You can use delimited identifiers (with double quotation marks) to prevent such conversion. You must use delimited identifiers when a table name contains blanks or special characters.

The table name can include Double Byte Character Set characters.

Restriction: The system-created data files that correspond to tables created and named by user names do not distinguish between upper and lowercase characters. For example, the data file for a table named TB is named DSY_TB. The data file for a table named "tb" is also DSY_TB. Therefore, to ensure data integrity, it is strongly recommended that you do not name a table using a series of characters identical, except for character case, to an existing table name.

WITH ENCRYPTION
Creates an encrypted user table. To encrypt a table, you must be authenticated and connected. You must be explicitly granted encryption. (For more information, see GRANT.)

A user table can be encrypted only at the time it is created. After a table is created, encryption cannot be added or removed except by deleting the table.

column-name
Names a column of the table. The name can be up to 18 bytes long. The name cannot be qualified and the same name cannot be used for more than one column of the table.

Column names are converted to uppercase before being stored in the catalog. You can use delimited identifiers (with double quotation marks) to prevent such conversion. You must also use delimited identifiers when a column name contains blanks or special characters.

The column name can include DBCS characters.

data-type
Is one of the types in the following list. Use:
INTEGER  or  INT
For a four-byte signed integer in the range of 2147483647 to -2147483648.
SMALLINT
For a two-byte signed integer in the range of -32768 to 32767.
DECIMAL(precision-integer, scale-integer)
For a decimal number. The first integer is the precision of the number; that is, the total number of digits; it might range from 1 to 31. The second integer is the scale of the number; that is, the number of digits to the right of the decimal point; it might range from 0 to the precision of the number.
CHAR(integer)
For a fixed-length character string of length integer, which might range from 1 to 32767.
CHARACTER(integer)
For a fixed-length character string of length integer, which might range from 1 to 32767.
VARCHAR(integer)
For a varying-length character string of maximum length integer, which might range from 1 to 32767.
BLOB(integer)
For a binary large object string of the specified maximum length in bytes.

The length might be in the range of 1 byte to 32767 bytes.

integer is the maximum length.

DATE
For a date. An input value can be in one of the following formats: MM/DD/YYYY, YYYY-MM-DD, or DD.MM.YYYY. The date value is printed out in only the ISO format, YYYY-MM-DD.

The special register CURRENT DATE also produces the current date in ISO format.

TIME
For a time. An input value can be in one of the following formats: HH:MM AM (or PM), HH:MM:SS, HH.MM AM (or PM), or HH.MM.SS. The SS, seconds, is optional with HH:MM:SS or HH.MM.SS formats. A time value is printed out only in ISO format, HH:MM:SS.

The special register CURRENT TIME also produces the current time in ISO format.

TIMESTAMP
For a timestamp. An input value must be in the following format: YYYY-MM-DD-HH.MM.SS.ZZZZZZ. A timestamp value is printed out in the following format: YYYY-MM-DD-HH.MM.SS.ZZZZZZ.

The special register CURRENT TIMESTAMP also produces the current timestamp.

column-options
Defines additional options related to columns of the table.
NOT NULL
Prevents the column from containing null values.

If NOT NULL is not specified, the column can contain null values, and its default value is either the null value or the value provided by the DEFAULT clause.

PRIMARY KEY
This provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause.

See the description of PRIMARY KEY on page ***.

REFERENCES table-name
See the description of REFERENCES on page ***.
CHECK (check-condition)
See the description of CHECK on page ***.
DEFAULT
Provides a default value in the event that a value is not supplied on an INSERT statement.

Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column. If such a column is defined NOT NULL, then the column does not have a valid default.

constant
Specifies the constant as the default value for the column. The specified constant must:
  • Represent a value that could be assigned to the column.
  • Not have non-zero digits beyond the scale of the column data type if the constant is a decimal constant (for example, 1.234 cannot be the default for a DECIMAL(5,2) column).
datetime-special-register
Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT as the default for the column. The data type of the column must be the data type that corresponds to the special register specified (for example, data type must be DATE when CURRENT DATE is specified).
GENERATED ALWAYS AS IDENTITY
When creating a table, a user can specify a column as "GENERATED ALWAYS AS IDENTITY". Subsequently, the value of this column will be generated by DB2 Everyplace each time the user performs an INSERT or INSERT with sub-SELECT. This column has to be a numeric type, (INTEGER, SMALLINT, or DECIMAL type), and DB2 Everyplace automatically generates unique serial numbers starting from 1, incremented by 1 each time.

The generated value for IDENTITY column starts from 1, and increases by 1 each time a row is inserted into the table. Thus, uniqueness is guaranteed, although DB2 Everyplace does not automatically create an index on an IDENTITY column. If you want to have an index on an IDENTITY column, you must either create an index explicitly, or specify the column as PRIMARY KEY. When the range of the values of an IDENTITY column is exhausted (the maximum value is reached), further INSERT statements will cause an error (SQLSTATE 23522). The maximum value of an IDENTITY column of INT and SMALLINT types are the maximum values allowed by those 2 types. The maximum value of an IDENTITY column of a DECIMAL type is determined by (1) definition of the data type (precision, scale) and (2) maximum value allowed for IDENTITY column: 2.15* (10^18) (19 decimal digits). The smaller of the (1) and (2) is the range limit. For an IDENTITY column of a DECIMAL type, the value's fractional part is always 0, and the integral part is increased by 1 each time.

The IDENTITY specification can only be defined on columns whose data type is one of the 3 numeric types: INT, SMAIINT, DECIMAL. Otherwise, an error is raised (SQLSTATE 42815). There can be at most one IDENTITY column per table (otherwise error SQLSTATE 428C1). The user can not provide a value for an IDENTITY column in an INSERT statement (must default to DB2 Everyplace system generated value), nor can the user UPDATE an IDENTITY column.

PRIMARY KEY (column-name, ...)
Defines a primary key composed of the identified columns. The clause must not be specified more than once and the identified columns must be defined as NOT NULL. Each column-name must identify a column of the table, and the same column must not be identified more than once.

The number of identified columns must not exceed 8.

A unique index will be automatically created on the specified columns.

Only one primary key can be defined on a table.

The length attribute of each specified column must not be greater than 1024 bytes.

referential-constraint
Defines a referential constraint.
FOREIGN KEY (column-name, ...)
Defines a referential constraint with the specified constraint-name.

Let T1 denote the object table of the statement. The foreign key of the referential constraint is composed of the identified columns. Each name in the list of column names must identify a column of T1, and the same column must not be identified more than once. The number of identified columns must not exceed 8. Foreign keys are not enforced by DB2 Everyplace.

REFERENCES table-name
The table specified in a REFERENCES clause must identify a base table that is described in the catalog, but must not identify a catalog table.

A referential constraint is a duplicate if its foreign key is the same as the foreign key table of a previously specified referential constraint.

In the following discussion, let T2 denote the identified parent table, and let T1 denote the table being created.

The specified foreign key must have the same number of columns as the parent key of T2 and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule. Foreign keys are not enforced by DB2 Everyplace.

CHECK (check-condition)
Defines a check constraint. A check-condition is a search condition. A column reference must be a column of the table being created. Values being inserted or updated into a table must satisfy any check constraints.

If a check constraint is specified as part of a column-definition then a column reference can be made only to the same column. Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement. Check constraints are not checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined.

The check-condition "IS NOT NULL" can be specified, however it is recommended that nullability be enforced directly using the NOT NULL attribute of a column. For example, CHECK (salary + bonus > 30000) is accepted if salary is set to NULL, because CHECK constraints must be either satisfied or unknown and in this case salary is unknown. However, CHECK (salary IS NOT NULL) would be considered false and a violation of the constraint if salary is set to NULL.

Check constraints are enforced when rows in the table are inserted or updated.

All check constraints defined in a CREATE TABLE statement are combined and stored in the system catalog. DB2 Everyplace has a limit of 512 bytes for this combined check constraint.

Rules

Notes

Example

Create table EMPLOYEE with column names EMPNO, FIRSTNAME, LASTNAME, DEPT, PHONENO, SALARY, and HIREDATE. CHAR means that the column will contain character data. NOT NULL means that the column cannot contain a null value. VARCHAR means that the column will contain varying-length character data. The primary key consists of the column EMPNO. 

     CREATE TABLE EMPLOYEE
       (EMPNO       CHAR(3)    PRIMARY KEY,
        FIRSTNAME   VARCHAR(12)   NOT NULL,
        LASTNAME    VARCHAR(15)   NOT NULL,
        DEPT        CHAR(3),
        PHONENO     CHAR(4),
        SALARY      INT,
        HIREDATE    DATE)

Related reference