The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table, such as its primary key or check constraints.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
If a subtable is being defined, the authorization ID must be the same as the definer of the root table of the table hierarchy.
To define a foreign key, the privileges held by the authorization ID of the statement must include one of the following on the parent table:
To define a summary table (using a fullselect) the privileges held by the authorization ID of the statement must include at least one of the following on each table or view identified in the fullselect:
>>-CREATE--+----------+---TABLE--table-name--------------------->
'-SUMMARY--'
>-----+-| element-list |------------------------------+--------->
+-OF--type-name1--+--------------------------+--+
| '-| typed-table-options |--' |
+-| summary-table-definition |------------------+
'-LIKE--+-table-name1-+---+-------------------+-'
+-view-name---+ '-| copy-options |--'
'-nickname----'
.-DATA CAPTURE NONE----.
>----*--+----------------------+--*----------------------------->
'-DATA CAPTURE CHANGES-'
>-----+-----------------------------------------------+--*------>
'-IN--tablespace-name1--| tablespace-options |--'
>-----+------------------------------------------------------------+>
| .-,---------. |
| V | .-USING HASHING--. |
+-PARTITIONING KEY--(-----column---+---)--+----------------+-+
'-REPLICATED-------------------------------------------------'
>----*--+----------------------+---*---------------------------><
'-NOT LOGGED INITIALLY-'
element-list
.-,---------------------------------.
V |
|---(------+-| column-definition |------+--+---)----------------|
+-| unique-constraint |------+
+-| referential-constraint |-+
'-| check-constraint |-------'
typed-table-options
|---+----------------------------+---+-------------------------+->
+-HIERARCHY--hierarchy-name--+ '-| typed-element-list |--'
'-| under-clause |-----------'
>---------------------------------------------------------------|
under-clause
|---UNDER--supertable-name--INHERIT SELECT PRIVILEGES-----------|
typed-element-list
.-,--------------------------------.
V |
|---(------+-| OID-column-definition |-+--+---)-----------------|
+-| with-options |----------+
+-| unique-constraint |-----+
'-| check-constraint |------'
summary-table-definition
|--+----------------------------+---AS--(--fullselect--)--| summary-table-options |-->
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
>---------------------------------------------------------------|
summary-table-options
|---+-DEFINITION ONLY--+-------------------+-+------------------|
| '-| copy-options |--' |
'-| refreshable-table-options |----------'
copy-options
|---*----+----------------------------------------+--*---------->
| .-COLUMN-. |
'--+-INCLUDING-+--+--------+--DEFAULTS---'
'-EXCLUDING-'
.-COLUMN ATTRIBUTES-.
.-EXCLUDING IDENTITY--+-------------------+--.
>-----+--------------------------------------------+--*---------|
| .-COLUMN ATTRIBUTES-. |
'-INCLUDING IDENTITY-+-------------------+---'
refreshable-table-options
|---DATA INITIALLY--DEFERRED--REFRESH--+-DEFERRED--+------------>
'-IMMEDIATE-'
.-ENABLE QUERY OPTIMIZATION--.
>-------+----------------------------+--------------------------|
'-DISABLE QUERY OPTIMIZATION-'
tablespace-options
|--+----------------------------------+------------------------->
| (1) |
'-INDEX IN--tablespace-name2-------'
>-----+----------------------------+----------------------------|
'-LONG IN--tablespace-name3--'
column-definition
|---column-name----+---------------------+---------------------->
| (2) |
'-| data-type |-------'
>-----+---------------------+-----------------------------------|
'-| column-options |--'
column-options
.---------------------------------------------------------------------------------.
V |
|------+---------------------------------------------------------------------------+--+->
+-NOT NULL------------------------------------------------------------------+
| (3) |
+-| lob-options |-----------------------------------------------------------+
| (4) |
+-| datalink-options |------------------------------------------------------+
| (5) |
+-SCOPE--+-typed-table-name-+-----------------------------------------------+
| '-typed-view-name--' |
+-+-----------------------------------+---+-+-PRIMARY KEY-+---------------+-+
| | (6) | | '-UNIQUE------' | |
| '-CONSTRAINT-------constraint-name--' +-| references-clause |---------+ |
| '-CHECK--(--check-condition--)--' |
| (7) |
+-| column-default-spec |---------------------------------------------------+
| (8) |
'-INLINE LENGTH--integer----------------------------------------------------'
>---------------------------------------------------------------|
Notes:
data-type |--+-SMALLINT-----------------------------------------------------------------------+-> +-+-INTEGER-+--------------------------------------------------------------------+ | '-INT-----' | +-BIGINT-------------------------------------------------------------------------+ +-+-FLOAT--+----------------+-+--------------------------------------------------+ | | '-(--integer--)--' | | | +-REAL----------------------+ | | | .-PRECISION-. | | | '-DOUBLE-+-----------+------' | +--+-DECIMAL-+---+--------------------------------+------------------------------+ | +-DEC-----+ '-(--integer--+-----------+---)--' | | +-NUMERIC-+ '-,integer--' | | '-NUM-----' | +--+--+-CHARACTER-+---+------------+----------------+---+----------------------+-+ | | '-CHAR------' '-(integer)--' | | (1) | | | +--+-VARCHAR-------------------+--(--integer--)--+ '--------FOR BIT DATA--' | | | '--+-CHARACTER-+---VARYING--' | | | | '-CHAR------' | | | '-LONG VARCHAR-----------------------------------' | | | +--+-BLOB---+--(--integer--+---+---)---------------------------------------------+ | +-CLOB---+ +-K-+ | | '-DBCLOB-' +-M-+ | | '-G-' | +-GRAPHIC--+------------+--------------------------------------------------------+ | '-(integer)--' | +-VARGRAPHIC--(integer)----------------------------------------------------------+ +-LONG VARGRAPHIC----------------------------------------------------------------+ +-DATE---------------------------------------------------------------------------+ +-TIME---------------------------------------------------------------------------+ +-TIMESTAMP----------------------------------------------------------------------+ +-DATALINK--+----------------+---------------------------------------------------+ | '-(--integer--)--' | +-distinct-type-name-------------------------------------------------------------+ +-structured-type-name-----------------------------------------------------------+ '-REF--(type-name2)--------------------------------------------------------------' >---------------------------------------------------------------|
Notes:
default-values
|--+-constant---------------------------------------------+-----|
+-datetime-special-register----------------------------+
+-USER-------------------------------------------------+
+-NULL-------------------------------------------------+
'-cast-function--(--+-constant------------------+---)--'
+-datetime-special-register-+
'-USER----------------------'
lob-options
.-LOGGED-----. .-NOT COMPACT--.
|---*--+------------+---*--+--------------+---*-----------------|
'-NOT LOGGED-' '-COMPACT------'
datalink-options
|---LINKTYPE URL------------------------------------------------>
.-NO LINK CONTROL------------------------------.
>----+----------------------------------------------+-----------|
'-FILE LINK CONTROL--+-| file-link-options |-+-'
'-MODE DB2OPTIONS-------'
file-link-options
|---*--INTEGRITY----ALL----*--READ PERMISSION--+-FS-+----------->
'-DB-'
>----*--WRITE PERMISSION--+-FS------+--*--RECOVERY--+-NO--+----->
'-BLOCKED-' '-YES-'
>----*--ON UNLINK--+-RESTORE-+---*------------------------------|
'-DELETE--'
column-default-spec
|---+-| default-clause |-----------------------------------------------+->
'-GENERATED--+-ALWAYS-----+---AS--+-| identity-clause |----------+-'
'-BY DEFAULT-' '-(--generation-expression--)--'
>---------------------------------------------------------------|
identity-clause
|---IDENTITY--+-------------------------------------------------------+->
| .-,-----------------------------------------. |
| V .-1----------------. | |
'-(------+-START WITH--+-numeric-constant-+---+--+---)--'
| .-1----------------. |
+-INCREMENT BY--+-numeric-constant-+-+
| .-CACHE--20----------------. |
'-+-NO CACHE-----------------+-------'
'-CACHE--integer-constant--'
>---------------------------------------------------------------|
references-clause
|--REFERENCES--table-name----+----------------------------+----->
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
>-----| rule-clause |-------------------------------------------|
rule-clause
.-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION--.
|--*--+-------------------------+---*--+----------------------+---*-->
'-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT---'
+-CASCADE--+
'-SET NULL-'
>---------------------------------------------------------------|
default-clause
.-WITH-.
|---+------+--DEFAULT--+---------------------+------------------|
'-| default-values |--'
unique-constraint
|---+------------------------------+---+-UNIQUE------+---------->
'-CONSTRAINT--constraint-name--' '-PRIMARY KEY-'
.-,--------------.
V |
>----(-----column-name---+---)----------------------------------|
referential-constraint
|---+-----------------------------------+--FOREIGN KEY---------->
| (1) |
'-CONSTRAINT--constraint-name-------'
.-,--------------.
V |
>----(-----column-name---+---)----| references-clause |---------|
check-constraint
|--+------------------------------+----------------------------->
'-CONSTRAINT--constraint-name--'
>----CHECK--(--check-condition--)-------------------------------|
OID-column-definition
|---REF IS--OID-column-name--USER GENERATED---------------------|
with-options
|---column-name--WITH OPTIONS---| column-options |--------------|
Notes:
Description
If UNDER is not specified, an object identifier column must be specified (refer to the OID-column-definition). This object identifier column is the first column of the table. The object ID column is followed by columns based on the attributes of type-name1.
The columns of the table include the object identifier column of the supertable with its type modified to be REF(type-name1), followed by columns based on the attributes of type-name1 (remember that the type includes the attributes of its supertype). The attribute names cannot be the same as the OID column name (SQLSTATE 42711).
Other table options including table space, data capture, not logged initially and partitioning key options cannot be specified. These options are inherited from the supertable (SQLSTATE 42613).
A list of column names must be specified if the result table of the fullselect has duplicate column names of an unnamed column (SQLSTATE 42908). An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.
Every select list element must have a name (use the AS clause for expressions - see select-clause for details) . The summary-table-options specified define attributes of the summary table. The option chosen also defines the contents of the fullselect as follows.
When DEFINITION ONLY is specified, any valid fullselect that does not reference a typed table or typed view can be specified.
When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include (SQLSTATE 428EC):
When REFRESH IMMEDIATE is specified:
The columns of the table are defined based on the definitions of the columns that result from the fullselect. If the fullselect references a single table in the FROM clause, select list items that are columns of that table are defined using the column name, data type, and nullability characteristic of the referenced table.
The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table, view or nickname.
Column default and identity column attributes may be included or excluded, based on the copy-attributes clauses. The implicit definition does not include any other attributes of the identified table, view or nickname. Thus the new table does not have any unique constraints, foreign key constraints, triggers, or indexes. The table is created in the table space implicitly or explicitly specified by the IN clause, and the table has any other optional clause only if the optional clause is specified.
If LIKE table-name is specified and table-name identifies a base table or declared temporary table, then INCLUDING COLUMN DEFAULTS is the default.
This clause is the default, except when LIKE table-name is specified and table-name identifies a base table or declared temporary table.
A table may have the following:
You can also specify:
If precision and scale are not specified, the default values of 5,0 are used. The words NUMERIC and NUM can be used as synonyms for DECIMAL and DEC.
The length may be in the range of 1 byte to 2 147 483 647 bytes.
If integer by itself is specified, that is the maximum length.
If integer K (in either upper or lower case) is specified, the maximum length is 1 024 times integer. The maximum value for integer is 2 097 152.
If integer M is specified, the maximum length is 1 048 576 times integer. The maximum value for integer is 2 048.
If integer G is specified, the maximum length is 1 073 741 824 times integer. The maximum value for integer is 2.
To create BLOB strings greater than 1 gigabyte, you must specify the NOT LOGGED option.
Any number of spaces is allowed between the integer and K, M, or G. Also, no space is required. For example, all the following are valid.
BLOB(50K) BLOB(50 K) BLOB (50 K)
The meaning of the integer K | M | G is the same as for BLOB.
To create CLOB strings greater than 1 gigabyte, you must specify the NOT LOGGED option.
The meaning of the integer K | M | G is similar to that for BLOB. The differences are that the number specified is the number of double-byte characters and that the maximum size is 1 073 741 823 double-byte characters.
To create DBCLOB strings greater than 1 gigabyte, you must specify the NOT LOGGED option.
The column in the table consists of "anchor values" that contain the reference information that is required to establish and maintain the link to the external data as well as an optional comment.
The length of a DATALINK column is 200 bytes. If integer is specified, it must be 200. If the length specification is omitted, a length of 200 bytes is assumed.
A DATALINK value is an encapsulated value with a set of built-in scalar functions. There is a function called DLVALUE to create a DATALINK value. The following functions can be used to extract attributes from a DATALINK value.
A DATALINK column has the following restrictions:
If a column is defined using a distinct type, then the data type of the column is the distinct type. The length and the scale of the column are respectively the length and the scale of the source type of the distinct type.
If a column defined using a distinct type is a foreign key of a referential constraint, then the data type of the corresponding column of the primary key must have the same distinct type.
If a column is defined using a structured type, then the static data type of the column is the structured type. The column may include values with a dynamic type that is a subtype of structured-type-name.
A column defined using a structured type cannot be used in a primary key, unique constraint, foreign key, index key or partitioning key (SQLSTATE 42962).
If a column is defined using a structured type, and contains a reference-type attribute at any level of nesting, that reference-type attribute is unscoped. To use such an attribute in a dereference operation, it is necessary to specify a SCOPE explicitly, using a CAST specification.
If a column is defined using a structured type with an attribute of type DATALINK, or a distinct type sourced on DATALINK, this column can only be null. An attempt to use the constructor function for this type will return an error (SQLSTATE 428ED) and so no instance of this type can be inserted into the column.
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 WITH DEFAULT clause.
LOBs greater than 1 gigabyte cannot be logged (SQLSTATE 42993) and LOBs greater than 10 megabytes should probably not be logged.
NOT LOGGED has no effect on a commit or rollback operation; that is, the database's consistency is maintained even if a transaction is rolled back, regardless of whether or not the LOB value is logged. The implication of not logging is that during a roll forward operation, after a backup or load operation, the LOB data will be replaced by zeros for those LOB values that would have had log records replayed during the roll forward. During crash recovery, all committed changes and changes rolled back will reflect the expected results. See the Administration Guide for the implications of not logging LOB columns.
ON UNLINK is not applicable since WRITE PERMISSION FS is used.
A scope must be specified for any column that is intended to be used as the left operand of a dereference operator or as the argument of the DEREF function. Specifying the scope for a reference type column may be deferred to a subsequent ALTER TABLE statement to allow the target table to be defined, usually in the case of mutually referencing tables.
If this clause is omitted, an 18-character identifier unique within the identifiers of the existing constraints defined on the table, is generated 76 by the system.
When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name may be used as the name of an index that is created to support the constraint.
A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) since the primary key is inherited from the supertable.
See PRIMARY KEY within the description of the unique-constraint below.
A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3) since unique constraints are inherited from the supertable.
See UNIQUE within the description of the unique-constraint below.
See references-clause under referential-constraint below.
The default INLINE LENGTH for a structured-type column is the inline length of its type (specified explicitly or by default in the CREATE TYPE statement). If INLINE LENGTH of the structured type is less than 292, the value 292 is used for the INLINE LENGTH of the column.
| Note: | The inline lengths of subtypes are not counted in the default inline length, meaning that instances of subtypes may not fit inline unless an explicit INLINE LENGTH is specified at CREATE TABLE time to account for existing and future subtypes. |
The explicit INLINE LENGTH value must be at least 292 and cannot exceed 32672 (SQLSTATE 54010).
If a column is defined as a DATALINK, then a default value cannot be specified (SQLSTATE 42613). The only possible default is NULL.
If the column is based on a column of a typed table, a specific default value must be specified when defining a default. A default value cannot be specified for the object identifier column of a typed table (SQLSTATE 42997).
If a column is defined using a distinct type, then the default value of the column is the default value of the source data type cast to the distinct type.
If a column is defined using a structured type, the default-clause cannot be specified (SQLSTATE 42842).
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.
If the value specified is not valid, an error (SQLSTATE 42894) is raised.
Although not explicitly required, a unique, single-column index should be defined on the generated column to ensure uniqueness of the values.
An identity column is implicitly NOT NULL.
If this value is negative, then the sequence of values for this identity column descends. If this value is positive, then the sequence of values for this identity column ascends. The default is 1.
If a new value is needed for the identity column and there are none available in the cache, then the allocation of the value involves waiting for the log. However, when a new value is needed for the identity column and there is an unused value in the cache, the allocation of that identity value can be made quicker by not performing the logging.
In the event of a database deactivation, either normally 79 or due to a system failure, all cached sequence values that have not been used in committed statements are lost. The value specified for the CACHE option is the maximum number of values for the identity column that could be lost in case of database deactivation.
The minimum value is 2 and the maximum value is 32767 (SQLSTATE 42815). The default is CACHE 20.
When this option is specified, the values of the identity column are not stored in the cache. In this case, every request for a new identity value results in logging.
The data type for the column is based on the result data type of the generation-expression. A CAST specification can be used to force a particular data type and to provide a scope (for a reference type only). If data-type is specified, values are assigned to the column under the assignment rules described in Language Elements. A generated column is implicitly considered nullable, unless the NOT NULL column option is used. The data type of a generated column must be one for which equality is defined. This excludes columns of LONG VARCHAR, LONG VARGRAPHIC, LOB data types, DATALINKs, structured types, and distinct types based on any of these types (SQLSTATE 42962).
If an option is already specified as part of the type definition (in CREATE TYPE), the options specified here override the options in CREATE TYPE.
If the table is a typed table, then this option is not supported (SQLSTATE 428DH or 42HDR).
If the table is defined to allow data on a partition other than the catalog partition (multiple partition nodegroup or nodegroup with a partition other than the catalog partition), then this option is not supported (SQLSTATE 42997).
If the schema name (implicit or explicit) of the table is longer than 18 bytes, then this option is not supported (SQLSTATE 42997).
Further information about using replication can be found in the Administration Guide and the Replication Guide and Reference.
IF table space IBMDEFAULTGROUP over which the user has USE privilege
exists with sufficient page size
THEN choose it
ELSE IF a table space over which the user has USE privilege
exists with sufficient page size
(see below when multiple table spaces qualify)
THEN choose it
ELSE issue an error (SQLSTATE 42727).
If more than one table space is identified by the ELSE IF condition, then choose the table space with the smallest sufficient page size over which the authorization ID of the statement has USE privilege. When more than one table space qualifies, preference is given according to who was granted the USE privilege:
If more than one table space still qualifies, the final choice is made by the database manager.
Determination of the table space may change when:
The sufficient page size of a table is determined by either the byte count of the row or the number of columns. See Row Size for more information.
Note that specifying which table space will contain a table's index can only be done when the table is created. The checking of USE privilege over the table space for the index is only carried out at table creation time. The database manager will not require that the authorization ID of a CREATE INDEX statement have USE privilege on the table space when an index is created later.
Note that specifying which table space will contain a table's long and LOB columns can only be done when the table is created. The checking of USE privilege over the table space for the long and LOB columns is only carried out at table creation time. The database manager will not require that the authorization ID of an ALTER TABLE statement have USE privilege on the table space when a long or LOB column is added later.
If this clause is not specified, and this table resides in a multiple partition nodegroup, then the partitioning key is defined as follows:
If none of the columns satisfy the requirement of the default partitioning key, the table is created without one. Such tables are allowed only in table spaces defined on single-partition nodegroups.
For tables in table spaces defined on single-partition nodegroups, any collection of non-long type columns can be used to define the partitioning key. If you do not specify this parameter, no partitioning key is created.
For restrictions related to the partitioning key, see Rules.
All catalog changes and storage related information are logged, as are all operations that are done on the table in subsequent units of work.
A foreign key constraint cannot be defined on a table that references a parent with the NOT LOGGED INITIALLY attribute. This clause cannot be specified when creating a subtable (SQLSTATE 42613).
| Note: | A rollback to savepoint request cannot be issued in the same unit of work as the creation of a NOT LOGGED INITIALLY table. This will result in an error (SQLSTATE 40506), and the entire unit of work will be rolled back. |
The number of identified columns must not exceed 16 and the sum of their stored lengths must not exceed 1024 (refer to Byte Counts for the stored lengths). The length of any individual column must not exceed 255 bytes. This length is for the data only and is not affected by the null byte, should it be present. The maximum data length of a column is 255 bytes, whether the column is nullable or not. No LOB, LONG VARCHAR, LONG VARGRAPHIC, DATALINK, distinct type based on one of these types, or structured type may be used as part of a unique key, even if the length attribute of the column is small enough to fit within the 255 byte limit (SQLSTATE 42962).
The set of columns in the unique key cannot be the same as the set of columns of the primary key or another unique key (SQLSTATE 01543). 81
A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3) since unique constraints are inherited from the supertable.
The description of the table as recorded in the catalog includes the unique key and its unique index. A unique index will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.
The number of identified columns must not exceed 16 and the sum of their stored lengths must not exceed 1024 (refer to Byte Counts for the stored lengths). The length of any individual column must not exceed 255 bytes. This length is for the data only and is not affected by the null byte, should it be present. The maximum data length of a column is 255 bytes, whether the column is nullable or not. No LOB, LONG VARCHAR, LONG VARGRAPHIC, DATALINK, distinct type based on one of these types, or structured type may be used as part of a primary key, even if the length attribute of the column is small enough to fit within the 255 byte limit (SQLSTATE 42962).
The set of columns in the primary key cannot be the same as the set of columns of a unique key (SQLSTATE 01543). 81
Only one primary key can be defined on a table.
A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) since the primary key is inherited from the supertable.
The description of the table as recorded in the catalog includes the primary key and its primary index. A unique index will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.
If the table has a partitioning key, the columns of a unique-constraint must be a superset of the partitioning key columns; column order is unimportant.
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 16 and the sum of their stored lengths must not exceed 1024 (refer to Byte Counts for the stored lengths). No LOB, LONG VARCHAR, LONG VARGRAPHIC, DATALINK, distinct type based on one of these types, or structured type column may be used as part of a foreign key (SQLSTATE 42962). There must be the same number of foreign key columns as there are in the parent key and the data types of the corresponding columns must be compatible (SQLSTATE 42830). Two column descriptions are compatible if they have compatible data types (both columns are numeric, character strings, graphic, date/time, or have the same distinct type).
A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key and parent table of a previously specified referential constraint. Duplicate referential constraints are ignored and a warning is issued (SQLSTATE 01543).
In the following discussion, let T2 denote the identified parent table and let T1 denote the table being created 82 (T1 and T2 may be the same table).
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.
The list of column names must match the set of columns (in any order) of the primary key or a unique constraint that exists on T2 (SQLSTATE 42890). If a column name list is not specified, then T2 must have a primary key (SQLSTATE 42888). Omission of the column name list is an implicit specification of the columns of that primary key in the sequence originally specified.
The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.
SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.
A cycle involving two or more tables must not cause a table to be delete-connected to itself unless all of the delete rules in the cycle are CASCADE. Thus, if the new relationship would form a cycle and T2 is already delete connected to T1, then the constraint can only be defined if it has a delete rule of CASCADE and all other delete rules of the cycle are CASCADE.
If T1 is delete-connected to T2 through multiple paths, those relationships in which T1 is a dependent and which form all or part of those paths must have the same delete rule and it must not be SET NULL. The NO ACTION and RESTRICT actions are treated identically. Thus, if T1 is a dependent of T3 in a relationship with a delete rule of r, the referential constraint cannot be defined when r is SET NULL if any of these conditions exist:
If r is other than SET NULL, the referential constraint can be defined, but the delete rule that is implicitly or explicitly specified in the FOREIGN KEY clause must be the same as r.
In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.
The difference between NO ACTION and RESTRICT is described under CREATE TABLE in Notes.
If a check constraint is specified as part of a column-definition then a column reference can only be made 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 resulting in possible errors at execution time.
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. A check constraint defined on a table automatically applies to all subtables of that table.
Table 22. Valid DATALINK File Control Option Combinations
| INTEGRITY | READ PERMISSION | WRITE PERMISSION | RECOVERY | ON UNLINK |
|---|---|---|---|---|
| ALL | FS | FS | NO | Not applicable |
| ALL | FS | BLOCKED | NO | RESTORE |
| ALL | FS | BLOCKED | YES | RESTORE |
| ALL | DB | BLOCKED | NO | RESTORE |
| ALL | DB | BLOCKED | NO | DELETE |
| ALL | DB | BLOCKED | YES | RESTORE |
| ALL | DB | BLOCKED | YES | DELETE |
The following rules only apply to partitioned databases.
Table T1 is a parent of table T3, delete rule as noted below Table T2 is a parent of table T3, delete rule CASCADE CREATE VIEW V1 AS SELECT * FROM T1 UNION ALL SELECT * FROM T2 DELETE FROM V1
If table T1 is a parent of table T3 with delete rule of RESTRICT, a restrict violation will be raised (SQLSTATE 23001) if there are any child rows for parent keys of T1 in T3.
If table T1 is a parent of table T3 with delete rule of NO ACTION, the child rows may be deleted by the delete rule of CASCADE when deleting rows from T2 before the NO ACTION delete rule is enforced for the deletes from T1. If deletes from T2 did not result in deleting all child rows for parent keys of T1 in T3, then a constraint violation will be raised (SQLSTATE 23504).
Note that the SQLSTATE returned is different depending on whether the delete or update rule is RESTRICT or NO ACTION.
In practical terms, an inoperative summary table is one in which the summary table definition has been unintentionally dropped. For example, when an alias is dropped, any summary table defined using that alias is made inoperative. All packages dependent on the summary table are no longer valid.
Until the inoperative summary table is explicitly recreated or dropped, a statement using that inoperative summary table cannot be compiled (SQLSTATE 51024) with the exception of the CREATE ALIAS, CREATE TABLE, DROP TABLE, and COMMENT ON TABLE statements. Until the inoperative summary table has been explicitly dropped, its qualified name cannot be used to create another view, base table or alias. (SQLSTATE 42710).
An inoperative summary table may be recreated by issuing a CREATE TABLE statement using the definition text of the inoperative summary table. This summary table query text is stored in the TEXT column of the SYSCAT.VIEWS catalog. When recreating an inoperative summary table, it is necessary to explicitly grant any privileges required on that table by others, due to the fact that all authorization records on a summary table are deleted if the summary table is marked inoperative. Note that there is no need to explicitly drop the inoperative summary table in order to recreate it. Issuing a CREATE TABLE statement that defines a summary table with the same table-name as an inoperative summary table will cause that inoperative summary table to be replaced, and the CREATE TABLE statement will return a warning (SQLSTATE 01595).
Inoperative summary tables are indicated by an X in the VALID column of the SYSCAT.VIEWS catalog view and an X in the STATUS column of the SYSCAT.TABLES catalog view.
Table 23. Limits for Number of Columns and Row Size in Each table space Page Size
| Page Size | Row Size Limit | Column Count Limit |
|---|---|---|
| 4K | 4 005 | 500 |
| 8K | 8 101 | 1 012 |
| 16K | 16 293 | 1 012 |
| 32K | 32 677 | 1 012 |
The actual number of columns for a table may be further limited by the following formula:
If the table is created based on a structured type, an additional 4 bytes of overhead is reserved to identify rows of subtables regardless of whether or not subtables are defined. Also, additional subtable columns must be considered nullable for byte count purposes, even when defined as not nullable.
Maximum LOB Length LOB Descriptor Size
1 024 72
8 192 96
65 536 120
524 000 144
4 190 000 168
134 000 000 200
536 000 000 224
1 070 000 000 256
1 470 000 000 280
2 147 483 647 316
Examples
Example 1: Create table TDEPT in the DEPARTX table space. DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT are column names. CHAR means the column will contain character data. NOT NULL means that the column cannot contain a null value. VARCHAR means the column will contain varying-length character data. The primary key consists of the column DEPTNO.
CREATE TABLE TDEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
PRIMARY KEY(DEPTNO))
IN DEPARTX
Example 2: Create table PROJ in the SCHED table space. PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, and MAJPROJ are column names. CHAR means the column will contain character data. DECIMAL means the column will contain packed decimal data. 5,2 means the following: 5 indicates the number of decimal digits, and 2 indicates the number of digits to the right of the decimal point. NOT NULL means that the column cannot contain a null value. VARCHAR means the column will contain varying-length character data. DATE means the column will contain date information in a three-part format (year, month, and day).
CREATE TABLE PROJ
(PROJNO CHAR(6) NOT NULL,
PROJNAME VARCHAR(24) NOT NULL,
DEPTNO CHAR(3) NOT NULL,
RESPEMP CHAR(6) NOT NULL,
PRSTAFF DECIMAL(5,2) ,
PRSTDATE DATE ,
PRENDATE DATE ,
MAJPROJ CHAR(6) NOT NULL)
IN SCHED
Example 3: Create a table called EMPLOYEE_SALARY where any unknown salary is considered 0. No table space is specified, so that the table will be created in a table space selected by the system based on the rules descirbed for the IN tablespace-name1 clause.
CREATE TABLE EMPLOYEE_SALARY
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
EMPNO CHAR(6) NOT NULL,
SALARY DECIMAL(9,2) NOT NULL WITH DEFAULT)
Example 4: Create distinct types for total salary and miles and use them for columns of a table created in the default table space. In a dynamic SQL statement assume the CURRENT SCHEMA special register is JOHNDOE and the CURRENT PATH is the default ("SYSIBM","SYSFUN","JOHNDOE").
If a value for SALARY is not specified it must be set to 0 and if a value for LIVING_DIST is not specified it must to set to 1 mile.
CREATE DISTINCT TYPE JOHNDOE.T_SALARY AS INTEGER WITH COMPARISONS
CREATE DISTINCT TYPE JOHNDOE.MILES AS FLOAT WITH COMPARISONS
CREATE TABLE EMPLOYEE
(ID INTEGER NOT NULL,
NAME CHAR (30),
SALARY T_SALARY NOT NULL WITH DEFAULT,
LIVING_DIST MILES DEFAULT MILES(1) )
Example 5: Create distinct types for image and audio and use them for columns of a table. No table space is specified, so that the table will be created in a table space selected by the system based on the rules descirbed for the IN tablespace-name1 clause. Assume the CURRENT PATH is the default.
CREATE DISTINCT TYPE IMAGE AS BLOB (10M)
CREATE DISTINCT TYPE AUDIO AS BLOB (1G)
CREATE TABLE PERSON
(SSN INTEGER NOT NULL,
NAME CHAR (30),
VOICE AUDIO,
PHOTO IMAGE)
Example 6: Create table EMPLOYEE in the HUMRES table space. The constraints defined on the table are the following:
| Note: | If the columns included in the check constraints are nullable they could also
be NULL.
CREATE TABLE EMPLOYEE
(ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB CHAR(5) CHECK (JOB IN ('Sales','Mgr','Clerk')),
HIREDATE DATE,
SALARY DECIMAL(7,2),
COMM DECIMAL(7,2),
PRIMARY KEY (ID),
CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500)
)
IN HUMRES
|
Example 7: Create a table that is wholly contained in the PAYROLL table space.
CREATE TABLE EMPLOYEE .....
IN PAYROLL
Example 8: Create a table with its data part in ACCOUNTING and its index part in ACCOUNT_IDX.
CREATE TABLE SALARY.....
IN ACCOUNTING INDEX IN ACCOUNT_IDX
Example 9: Create a table and log SQL changes in the default format.
CREATE TABLE SALARY1 .....
or
CREATE TABLE SALARY1 .....
DATA CAPTURE NONE
Example 10: Create a table and log SQL changes in an expanded format.
CREATE TABLE SALARY2 .....
DATA CAPTURE CHANGES
Example 11: Create a table EMP_ACT in the SCHED table space. EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, and EMENDATE are column names. Constraints defined on the table are:
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5,2),
EMSTDATE DATE,
EMENDATE DATE,
CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO)
REFERENCES PROJECT (PROJNO) ON DELETE CASCADE
)
IN SCHED
A unique index called EMP_ACT_UNIQ is automatically created in the same schema to enforce the unique constraint.
Example 12: Create a table that is to hold information about famous goals for the ice hockey hall of fame. The table will list information about the player who scored the goal, the goaltender against who it was scored, the date and place, and a description. When available, it will also point to places where newspaper articles about the game are stored and where still and moving pictures of the goal are stored. The newspaper articles are to be linked so they cannot be deleted or renamed but all existing display and update applications must continue to operate. The still pictures and movies are to be linked with access under complete control of DB2. The still pictures are to have recovery and are to be returned to their original owner if unlinked. The movie pictures are not to have recovery and are to be deleted if unlinked. The description column and the three DATALINK columns are nullable.
CREATE TABLE HOCKEY_GOALS
( BY_PLAYER VARCHAR(30) NOT NULL,
BY_TEAM VARCHAR(30) NOT NULL,
AGAINST_PLAYER VARCHAR(30) NOT NULL,
AGAINST_TEAM VARCHAR(30) NOT NULL,
DATE_OF_GOAL DATE NOT NULL,
DESCRIPTION CLOB(5000),
ARTICLES DATALINK LINKTYPE URL FILE LINK CONTROL MODE DB2OPTIONS,
SNAPSHOT DATALINK LINKTYPE URL FILE LINK CONTROL
INTEGRITY ALL
READ PERMISSION DB WRITE PERMISSION BLOCKED
RECOVERY YES ON UNLINK RESTORE,
MOVIE DATALINK LINKTYPE URL FILE LINK CONTROL
INTEGRITY ALL
READ PERMISSION DB WRITE PERMISSION BLOCKED
RECOVERY NO ON UNLINK DELETE )
Example 13: Suppose an exception table is needed for the EMPLOYEE table. One can be created using the following statement.
CREATE TABLE EXCEPTION_EMPLOYEE AS
(SELECT EMPLOYEE.*,
CURRENT TIMESTAMP AS TIMESTAMP,
CAST ('' AS CLOB(32K)) AS MSG
FROM EMPLOYEE
) DEFINITION ONLY
Example 14: Given the following table spaces with the indicated attributes:
TBSPACE PAGESIZE USER USERAUTH ------------------ ----------- ------ -------- DEPT4K 4096 BOBBY Y PUBLIC4K 4096 PUBLIC Y DEPT8K 8192 BOBBY Y DEPT8K 8192 RICK Y PUBLIC8K 8192 PUBLIC Y
CREATE TABLE DOCUMENTS
(SUMMARY VARCHAR(1000),
REPORT VARCHAR(2000))
CREATE TABLE CURRICULUM
(SUMMARY VARCHAR(1000),
REPORT VARCHAR(2000),
EXERCISES VARCHAR(1500))
Example 15: Create a table with a LEAD column defined with the structured type EMP. Specify an INLINE LENGTH of 300 bytes for the LEAD column, indicating that any instances of LEAD that cannot fit within the 300 bytes are stored outside the table (separately from the base table row, similar to the way LOB values are handled).
CREATE TABLE PROJECTS (PID INTEGER,
LEAD EMP INLINE LENGTH 300,
STARTDATE DATE,
...)
Example 16: Create a table DEPT with five columns named DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, and LOCATION. Column DEPT is to be defined as an IDENTITY column such that DB2 will always generate a value for it. The values for the DEPT column should begin with 500 and increment by 1.
CREATE TABLE DEPT
(DEPTNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500, INCREMENT BY 1),
DEPTNAME VARCHAR (36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT SMALLINT NOT NULL,
LOCATION CHAR(30))