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.
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--+-------------------+---+-------------------------+-+
| '-| under-clause |--' '-| typed-element-list |--' |
'-| summary-table-definition |----------------------------------------'
.-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 |-------'
under-clause
|---UNDER--supertable-name--INHERIT SELECT PRIVILEGES-----------|
typed-element-list
.-,---------------------------------.
V |
|---(------+-| OID-column-definition |--+--+---)----------------|
+-| with-options |-----------+
+-| unique-constraint |------+
+-| referential-constraint |-+
'-| check-constraint |-------'
summary-table-definition
|--AS--(--fullselect--)--| summary-table-options |--------------|
summary-table-options
|---+-DEFINITION ONLY----------------------------------+--------|
'-DATA INITIALLY--DEFERRED--REFRESH--+-DEFERRED--+-'
'-IMMEDIATE-'
tablespace-options
|--+----------------------------------+------------------------->
| (1) |
'-INDEX IN--tablespace-name2-------'
>-----+----------------------------+----------------------------|
'-LONG IN--tablespace-name3--'
column-definition
|---column-name----| data-type |--+---------------------+-------|
'-| column-options |--'
column-options
.---------------------------------------------------------------------------------.
V |
|------+---------------------------------------------------------------------------+--+->
+-NOT NULL------------------------------------------------------------------+
+-| default-clause |--------------------------------------------------------+
| (2) |
+-| lob-options |-----------------------------------------------------------+
| (3) |
+-| datalink-options |------------------------------------------------------+
| (4) |
+-SCOPE--+-typed-table-name-+-----------------------------------------------+
| '-typed-view-name--' |
'-+-----------------------------------+---+-+-PRIMARY KEY-+---------------+-'
| (5) | | '-UNIQUE------' |
'-CONSTRAINT-------constraint-name--' +-| references-clause |---------+
'-CHECK--(--check-condition--)--'
>---------------------------------------------------------------|
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-------------------------------------------------------------+ '-REF--(type-name2)--------------------------------------------------------------' >---------------------------------------------------------------|
Notes:
default-clause
.-WITH-.
|--+------+--DEFAULT--+---------------------+-------------------|
'-| default-values |--'
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--'
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-'
>---------------------------------------------------------------|
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) as 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).
Defines the query in which the table is based. The summary-table-options define attributes of the summary table. The option chosen also defines the contents of the fullselect as follows.
When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include:
When REFRESH IMMEDIATE is specified, the fullselect must be a subselect and cannot include:
Furthermore, for REFRESH IMMEDIATE, the base table must have at least one unique index defined and the SELECT clause must include all of the columns of this unique index.
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 the column is 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 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.
If a column is defined as a DATALINK, then a default value cannot be specified. The only possible default is NULL.
If the column is based on an attribute of a structured type, a specific default value must be specified when defining a default.
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.
Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column.
If the value specified is not valid, an error (SQLSTATE 42894) is raised.
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 (63) 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.
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.
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 length attributes must not exceed 255 minus the number of columns that allow null values. No LOB, LONG VARCHAR, LONG VARGRAPHIC or DATALINK 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 (65) (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.
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.
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 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).
Further information about using replication can be found in the Administration Guide and the DB2 Replication Guide and Reference.
IF table space IBMDEFAULTGROUP exists with sufficient page size THEN use it ELSE IF user created table space exists with sufficient page size THEN use it ELSE IF table space USERSPACE1 exists with sufficient page size THEN use it ELSE IF USERSPACE8K exists with sufficient page size THEN use it ELSE issue an error (SQLSTATE 42727).
The sufficient page size of a table is determined by either the byte count of the row or the number of columns. Refer to Row Size for more information. If the table includes one or more LOB columns, then only a 4K page size table space can be used. If the number of columns or the row size would require an 8K page size table space, then a default cannot be select and an error is returned (SQLSTATE 42997). In this situation an 8K page size DMS table space should be specified with a 4K page size DMS table space in the LONG IN clause.
Note that specifying which table space will contain a table's index can only be done when the table is created.
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: | An error in any operation in the unit of work in which the table is created will result in the entire unit of work being rolled back. |
Table 18. 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 19. Limits for Number of Columns and Row Size in Each Table Space Page Size
| Page Size | Row Size Limit | Column Count Limit |
|---|---|---|
| 4K | 4005 | 500 |
| 8K | 8101 | 1012 |
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 )
(61) Observe that it is not possible to specify the FOR BIT DATA clause for CLOB columns. However, a CHAR FOR BIT DATA string can be assigned to a CLOB column and a CHAR FOR BIT DATA string can be concatenated with a CLOB string.
(62) With DB2 Universal Database, the file is assigned to a special predefined "dfmunknown" user id.
(63) The identifier is formed of "SQL" followed by a sequence of 15 numeric characters generated by a timestamp-based function.
(64) If LANGLEVEL is SQL92E or MIA then an error is returned, SQLSTATE 42891.
(65) or altered, in the case where this clause is referenced from the description of the ALTER TABLE statement.