CREATE TABLE clauses have been extended to include options for defining and dropping table check constraints, and specifying columns with user-defined defaults.
This statement can be embedded in an application program or issued interactively.
To execute the CREATE TABLE command, users must possess one (or more) privileges listed in at least one of the following:
Additional privileges might be required in the following conditions:
If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.
If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process.
Bold text indicates clauses or options that are supported only syntactically.
CREATE TABLE table-name
{
(column-definition
[unique-constraint]
[referential-constraint]
[select-command]
[check-constraint] [,...])
| LIKE {table-name | view-name} [INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
| materialized-query-definition
}
[WITH RESTRICT ON DROP]
[{IN [database-name.]tablespace-name
| IN DATABASE database-name}]
[partitioning-clause]
[EDITPROC program-name]
[VALIDPROC program-name]
[AUDIT {NONE | CHANGES | ALL}]
[OBID integer]
[DATA CAPTURE {NONE | CHANGES}]
[WITH RESTRICT ON DROP]
[CCSID {ASCII | EBCDIC | UNICODE}]
[{VOLATILE | NOT VOLATILE} [CARDINALITY]]
unique-constraint:
{UNIQUE | PRIMARY KEY}(column-name[,...])
referential-constraint:
FOREIGN KEY [constraint-name] (column-name[,...])
references-clause
references-clause:
REFERENCES referenced-table
[column-name ,...]
[ON DELETE {RESTRICT | NO ACTION | CASCADE | SET NULL}]
[ENFORCED | NOT ENFORCED]
[ENABLE QUERY OPTIMIZATION]
check-constraint:
[CONSTRAINT constraint-name] CHECK (check-condition)
column-definition:
column-name data-type
[NOT NULL]
[column-constraint]
[{UNIQUE | PRIMARY KEY}]
[FOR {BIT | SBCS | MIXED} DATA]
[default-clause]
[GENERATED {ALWAYS | BY DEFAULT} [as-identity-clause]
[references-clause]
[check-constraint]
[FIELDPROC program-name [(constant[,...])]]
[AS SECURITY LABEL]
default-clause:
[WITH] DEFAULT
[{constant | USER | CURRENT SQLID | NULL | cast-function-name ({constant | USER | CURRENT SQLID | NULL})}]
data-type:
{built-in-data-type | distinct-type-name}
built-in-data-type:
SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] }
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID
as-identity-clause:
AS IDENTITY
[({START WITH {numeric-constant | 1}
| INCREMENT BY {numeric-constant | 1}
| MINVALUE numeric-constant | NOMINVALUE
| MAXVALUE numeric-constant | NOMAXVALUE
| CYCLE | NOCYCLE
| ORDER | NO ORDER
| {CACHE 20 | NO CACHE | CACHE integer}}][,..])]
partitioning-clause:
PARTITION BY [RANGE] (partition-expression[,...]) (partition-element[,...])
partition-expresion:
column-name [NULLS LAST] [ASC | DESC]
partition-element:
PARTITION integer ENDING [AT] (constant[,...]) [INCLUSIVE]
materialized-query-definition:
[(column-name[,...])] AS (fullselect)
{WITH NO DATA [copy-options] | refreshable-table-options}
copy-options:
[EXCLUDING IDENTITY [COLUMN ATTRIBUTES] |
INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
[EXCLUDING [COLUMN] DEFAULTS | INCLUDING [COLUMN] DEFAULTS | USING TYPE DEFAULTS]
refreshable-table-options:
DATA INITIALLY DEFERRED REFRESH DEFERRED
[[MAINTAINED BY SYSTEM | MAINTAINED BY USER]
[ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] ...]
[WITH] DEFAULT clause: names the default value for the column. The value of the constant must conform to the rules for assigning that value to the column. A default value can be specified for a column with a data type of LONG VARCHAR or LONG VARGRAPHIC and a maximum length of 254 bytes.
A character or graphic string constant must be short enough so that its UTF-8 representation requires no more than 1536. In addition, a hexadecimal graphic string constant (GX) cannot be specified.
To conform with DB2 mainframe-specific operations, the XDB Server also records the FOR SBCS DATA and FOR MIXED DATA clause options in the appropriate system catalog tables.
NOT NULL is an optional instruction that prevents the column from containing null values.
NOT NULL WITH DEFAULT prevents columns from containing null values. When an attempt is made to place a NULL value in such a column, a default value is inserted. The default value is dependent on the data type as shown in the following table.
[WITH] DEFAULT clause: In a given column definition neither NOT NULL nor DEFAULT can be specified more than once. Both NOT NULL and DEFAULT cannot be specified. Omitting NOT NULL and DEFAULT implies that DEFAULT NULL is intended.
| Data Type | Default Value |
|---|---|
| Numeric | 0 |
| Date | current date |
| Time | current time |
| Timestamp | current timestamp |
| Character | Blanks |
| Varchar | Blanks |
| Graphic | Blanks |
Also defines a table CHECK constraint within the CONSTRAINT...CHECK clause. The constraint-name is an SQL long identifier. A constraint-name cannot be specified more than once for the same table (see CONSTRAINT keyword).
A check condition can consist of a single predicate, or two or more predicates joined by AND or OR. The first operand of each predicate must be the name of a column of the table. The second operand can be a column name or a constant. A check-condition can evaluate to unknown if a column that is an operand of the predicate is null. A check-condition that evaluates to unknown does not violate the check constraint (see CHECK clause).
For conformance with the SQL standard, if CHECK is specified in the column definition of column C, no columns other than C should be referenced in the check condition of the check constraint. The effect is the same as if the check condition were specified as a separate clause.
The ON DELETE or ON UPDATE (XDB mode only) clause tells the system how to handle dependent records in this table when a corresponding record in the parent table is being deleted. The RESTRICT option (default) prevents the record from being deleted from the parent table. The CASCADE option deletes or updates the dependent records from the current table, and allows the deletion or updating of the parent record. The SET NULL option causes the foreign key fields in the dependent records to be set to NULL before the parent record is deleted.
XDB Server tables can be created using the CREATE TABLE command. Tables can also be created using SQLWizard. For more information on SQLWizard, see the SQLWizard User's Guide. The XDB Server supports a CREATE with SELECT command which allows a table to be created and populated in one command execution. The CREATE LIKE command allows the creation of a table using an existing table or view specification. See CREATE LIKE below.
The following example creates the table INVENT:
CREATE TABLE invent
(item_no SMALLINT NOT NULL,
description CHAR (12),
model CHAR (6),
type CHAR 12,
weight FLOAT,
qoh INTEGER,
unit_cost DECIMAL(6,3),
price MONEY)
The INVENT table will have the structure shown in the following table:
| Column Name | Data Type |
|---|---|
| item_no | SMALLINT (nulls not permitted) |
| Description | CHAR 12 |
| Model | CHAR 6 |
| Type | CHAR 12 |
| Weight | FLOAT |
| Qoh | INTEGER |
| unit_cost | DECIMAL(6,3) |
| Price | MONEY |
Once a table has been created, data can be entered or indexes created. The column types and the number of columns can be changed using the ALTER TABLE command. The table name can be changed by the RENAME command. The table can also be altered or renamed using SQLWizard (applies to Windows environments only). (For more information on SQLWizard, see the SQLWizard User's Guide.)
With DB2 V7, XDB locations (either EBCDIC or ASCII) can now contain Unicode data. If you create a table with CCSID UNICODE, all character data in corresponding tables is stored in UTF-8 format and all graphic data is stored in UTF-16 format.
| SBCS | Column holds single-byte data. |
| MIXED | Column holds mixed data. Do not specify MIXED if the value of field MIXED DATA on installation panel DSNTIPF is NO. |
| BIT | Column holds BIT data. Do not specify BIT for a CLOB column. |
If you do not specify the FOR clause, the column is defined with a default subtype. The default is SBCS when the value of field MIXED DATA on installation panel DSNTIPF is NO. The default is MIXED when the value is YES.
A security label column is always considered SBCS data, regardless of the encoding scheme of the table.
XDB Server operating environment does not support the CLOB data type at this time.
For a row ID type.
A table can have only one ROWID column. The values in a ROWID column are unique for every row in the table and cannot be updated. You must specify NOT NULL with ROWID.
Prevents the column from containing null values.
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.
The NOT NULL clause must be specified with this clause. Each column-name must be an unqualified name that identifies a column of the table except a LOB or ROWID column (including a distinct type that is based on a LOB or ROWID), and the same column must not be identified more than once. The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 -2m, where m is the number of varying-length columns in the key.
The table is marked as unavailable until its primary index is explicitly created unless the CREATE TABLE statement is processed by the schema processor. In that case, DB2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete.
Provides a shorthand method of defining a unique key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause.
The NOT NULL clause must be specified with this clause. UNIQUE cannot be specified more than once in a column definition and must not be specified if the PRIMARY KEY clause is specified in the column definition or if the definition is for a LOB column. The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 -2m, where m is the number of varying-length columns in the key.
Each column-name must be an unqualified name that identifies a column of the table except a LOB column, and the same column must not be identified more than once.
The default value assigned to the column in the absence of a value specified on INSERT or LOAD. Do not specify DEFAULT for a ROWID column, an identity column (a column that is defined AS IDENTITY) or a security label column (a column that is defined AS SECURITY LABEL); DB2 generates default values. If a value is not specified after DEFAULT, the default value depends on the data type of the column, as follows:
| Data Type | Default Value |
|---|---|
| Numeric | 0 |
| Fixed-length string | Blanks |
| Varying-length string | A string of length 0 |
| Date | CURRENT DATE |
| Time | CURRENT TIME |
| Timestamp | CURRENT TIMESTAMP |
| Distinct type | The default of the source data type |
A default value other than the one that is listed above can be specified in one of the following forms, except for a LOB column. The only form that can be specified for a LOB column is DEFAULT NULL. Unlike other varying-length strings, a LOB column can only have the default value of a zero-length string as listed above or null.
Specifies that XDB generates values for the column. GENERATED must be specified if the column is to be considered an identity column. If the data type of the column is a ROWID (or a distinct type that is based on a ROWID), the default is GENERATED ALWAYS.
| ALWAYS | Indicates that XDB will always generate a value for the column when a row is inserted into the table. ALWAYS is the recommended value unless you are using data propagation. |
| BY DEFAULT | Indicates that XDB will generate a value for the column when a row is inserted into the table unless a value is specified.
For a ROWID column, XDB uses a specified value only if it is a valid row ID value that was previously generated by XDB and the column has a unique, single-column index. Until this index is created on the ROWID column, the SQL INSERT statement and the LOAD utility cannot be used to add rows to the table. If the value of special register CURRENT RULES is 'STD' when the CREATE TABLE statement is processed, XDB implicitly creates the index on the ROWID column. The name of this index is 'I' followed by the first ten characters of the column name followed by seven randomly generated characters. If the column name is less than ten characters, XDB adds underscore characters to the end of the name until it has ten characters. The implicitly created index has the COPY NO attribute. For an identity column, XDB inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique, single-column index. Without a unique index, XDB can guarantee unique values only among the set of system-generated values. BY DEFAULT is the recommended value only when you are using data propagation. |
Specifies that the column will contain security label values. This also indicates that the table is defined with multilevel security with row level granularity. A table can have only one security label column. To define a table with a security label column, the primary authorization ID of the statement must have a valid security label, and the RACF SECLABEL class must be active. In addition, the following conditions are also required:
Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, DECIMAL with a scale of zero, or a distinct type based on one of these types).
An identity column is implicitly NOT NULL.
| START WITH numeric-constant | Specifies the first value for the identity column. The value can be any positive or negative value that could be assigned to the column without non-zero digits exisiting to the right of the decimal point. The default is 1. |
| INCREMENT BY numeric-constant | Specifies the interval between consecutive values of the identity column. The value can be any positive or negative value
(including 0) that does not exceed the value of a large integer constant, and could be assigned to the column without any
non-zero digits existing to the right of the decimal point. The default is 1.
If this value is negative, the values for the identity column descend. If this value is 0 or positive, the values for the identity column ascend. The default is 1. |
Specifies the minimum value at which a descending identity column either cycles or stops generating values or an ascending identity column cycles to after reaching the maximum value. The default is NO MINVALUE.
Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MINVALUE becomes one of the following:
Specifies the numeric constant that is the minimum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.
Specifies the maximum value at which an ascending identity column either cycles or stops generating values or a descending identity column cycles to after reaching the minimum value. The default is NO MAXVALUE.
Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MAXVALUE becomes one of the following:
Specifies the numeric constant that is the maximum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.
Specifies whether this identity column should continue to generate values after reaching either its maximum or minimum value.
Specifies that values will not be generated for the identity column after the maximum or minimum value has been reached. This is the default.
Specifies that values continue to be generated for the identity column after the maximum or minimum value has been reached. If this option is used, after an ascending identity column reaches the maximum value, it generates its minimum value. After a descending identity column reaches its minimum value, it generates its maximum value. The maximum and minimum values for the identity column determine the range that is used for cycling.
When CYCLE is in effect, duplicate values can be generated by DB2 for an identity column. However, if a unique index exists on the identity column and a non-unique value is generated for it, an error occurs.
This clause has no functional equivalent in the XDB Server operating environment.
Specifies whether the identity column values must be generated in order of request. The default is NO ORDER.
Specifies that the values do not need to be generated in order of request.
Specifies that the values are generated in order of request. Specifying ORDER may disable the caching of values. ORDER applies only to a single-application process.
In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for identity values from different DB2 members may not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the identity column, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested a value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that identity values are generated in strict numeric order among multiple DB2 members using the same identity column, specify the ORDER option.
Specifies the range partitioning scheme for the table (the columns used to partition the data). When this clause is specified, the table space is complete, and it is not necessary to create a partitioned index on the table. If this clause is used, the ENDING AT clause cannot be used on a subsequent CREATE INDEX statement for this table.
If this clause is specified, the IN database-name.table-space-name clause is required. This clause applies only to tables in a partitioned table space.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the key data over which the range is defined to determine the target data partition of the data.
Specifies the columns of the key. Each column-name must identify a column of the table. Do not specify more than 64 columns, the same column more than once, a LOB column, a column with a distinct type that is based on a LOB data type, or a qualified column name. The sum of length attributes of the columns must not be greater than 255 - n, where n is the number of columns that can contain null values.
Specifies that null values are treated as positive infinity for purposes of comparison.
Puts the entries in ascending order by the column. ASC is the default.
Puts the entries in descending order by the column.
Specifies ranges for a data partitioning key and the table space where rows of the table in the range will be stored.
integer is the physical number of a partition in the table space. A PARTITION clause must be specified for every partition of the table space. In this context, highest means highest in the sorting sequences of the columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC), the lowest actual value is highest in the sorting sequence.
This clause is only implemented syntactically in the XDB Server operating environment.
constant defines the limit key for a partition boundary. Specify at least one constant after ENDING AT in each PARTITION clause. You can use as many constants as there are columns in the key. The concatenation of all the constants is the highest value of the key for ascending and the lowest for descending. The use of constants to define key values is subject to the following rules:
Using fewer constants than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.
Specifies that the specified range values are included in the data partition.
The CREATE command may include an optional SELECT command (XDB mode only) that selects data from an existing table to insert into the new table. The order of the selected columns defines the column order of the new table. However, the number of columns selected does not need to match the number of columns defined for the new table. If the new table has more columns than the SELECT statement, the extra columns are assigned NULL values. If the new table has fewer columns than the SELECT command, the system uses only as many of the first columns as will fit into the new table. If no column names are given in the create command, the column names listed in the SELECT command are used to create the table.
Any search condition can be used in the SELECT statement including a join condition or a subselect. See WHERE Clause for more information on search conditions. The columns selected do not have to match the data type of the new table columns, but they must be type-compatible. The new table, for example, accepts floating point data in a column defined as an integer -- however, the fractional part of the data is truncated. See the ALTER TABLE command for a type compatibility matrix. When converting from character data to another type, if the conversion is not possible the new value will be NULL.
Three examples of CREATE with SELECT are given below:
CREATE TABLE parispno
(pno CHAR 2, pname CHAR 6, city CHAR 6,
weight DECIMAL(4,3))
SELECT pno, pname, city
FROM part
WHERE city = "PARIS"
CREATE TABLE lowinv
SELECT *
FROM partsupp
WHERE qty < 400
CREATE TABLE (a, b, c)
SELECT A.a, B.b, C.c
FROM A, B, C
WHERE A.x = B.x
AND B.x = C.x
When populating new tables with subselects, the subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.
Invalid:
CREATE TABLE tutorial.tutorial.sample SELECT * FROM tutorial.tutorial.employee WHERE e_no IN SELECT e_no FROM debloc.debbie.employee WHERE st = "MD";
Valid:
CREATE TABLE tutorial.tutorial.sample SELECT * FROM tutorial.tutorial.employee WHERE e_no IN SELECT e_no FROM tutorial.debbie.employee WHERE st = "MD";
The LIKE table-name or LIKE view-name clause is used to create a new table with the same definition as an existing table or view. The columns of the table will have exactly the same names and column specifications (data type, null permission, etc.) as the source table or view. However, neither the primary nor foreign keys, nor any check option existing on the source table or view are defined for the new table. Data from the source table is NOT transferred to the new table.
An example of the CREATE TABLE...LIKE command is:
CREATE TABLE personnel LIKE employee
Creating a table like a view
If the LIKE clause is specified and the definition of the table is being based on a view, the view must not include a ROWID column or an identity column. A view column is considered to be an identity column if the corresponding column of the table or view indirectly or directly maps to the name of an identity column in a base table with these exceptions:
Specifies a database-name alone (preceded by keyword IN DATABASE), or both a tablespace-name with an optional database-name qualifier (preceded by keyword IN). If a database is named, it must be described in the system catalog for the current location, and must not be the DSNDB06 catalog database.
If a table space name is not explicitly specified, a table space is created implicitly within database-name. In these cases, the XDB Server checks the privileges held by the user for either SYSADM or SYSCTRL authority for the location, DBADM, DBCTRL or DBMAINT authority for the database, or the CREATETS privilege for the database. The table space name is taken from the table name specified in the CREATE TABLE command. Except for the name, the other attributes of this table space match those that would result from a CREATE TABLESPACE statement minus all its optional clauses.
If tablespace-name is specified, this name must not reference an already existing table space that was implicitly created. If both a database and table space are specified, the table space must belong to the named database. If only a table space is named, it must belong to the default DSNDB04 database for the current location. If the table space references a stogroup, then any tables created with that table space name are stored in the path specified for the stogroup.
A subselect and an IN DATABASE clause cannot be used in the same CREATE TABLE statement. Instead, create the table in the database and then use an INSERT INTO command with a subselect to populate the table.
For example:
Invalid:
CREATE TABLE tutorial.debbie.employee
SELECT * FROM tutorial.tutorial.employee
WHERE st = "MD"
IN DATABASE debdb;
Valid:
CREATE TABLE tutorial.debbie.employee (e_no SMALLINT NOT NULL,
lname CHAR 10 NOT NULL,
fname CHAR 10 NOT NULL, street CHAR 20,
city CHAR 15, st CHAR 2, zip CHAR 5,
dept CHAR 4, payrate MONEY, com DECIMAL(2,2))
IN DATABASE debdb;
INSERT INTO tutorial.debbie.employee
SELECT * FROM tutorial.tutorial.employee
WHERE st = "MD";
Identifies the OBID to be used for this table. An OBID is the identifier for an object's internal descriptor.
The OBID keyword option is used with ROSHARE databases, and can be viewed by querying the SYSIBM.SYSTABLES catalog table. The integer must not identify an existing or previously used OBID of the database. If you omit OBID, DB2 generates a value.
The following statement retrieves the value of OBID:
SELECT OBID
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ccc' AND NAME = 'nnn';
Here, nnn is the table name and ccc is the table's creator
Defines a unique key composed of the identified columns. Each column name must be an unqualified name that identifies a column of the table, and the same column must not be identified more than once. A unique key is a duplicate if it is the same as the primary key or a previously defined unique key. A unique key on a single column can be defined in a column definition. In a given column definition neither UNIQUE nor PRIMARY KEY can be specified more than once, nor can UNIQUE and PRIMARY KEY both be specified for the same column.
A table can have at most one constraint condition specified as a PRIMARY KEY. The purpose of the primary key is to enforce entity integrity -- that is, to uniquely identify each row of a table. Note that the columns in the primary key must be defined as NOT NULL when the columns are specified.
You must create an index for a primary key or a unique key if the CREATE TABLE statement is processed by the XDB Server. XDB Server does not implicitly create an index.
A primary key on a single column can be defined in a column definition. In a given column definition neither UNIQUE nor PRIMARY KEY can be specified more than once, nor can UNIQUE and PRIMARY KEY both be specified for the same column.
You cannot create foreign keys on tables that are in a pending state. A table is in a pending state if a primary key clause has been specified but a unique index has not been built on those columns.
For example, to define the SNO and PNO columns as the primary key of the PARTSUPP table, use the command:
CREATE TABLE partsupp(sno SMALLINT NOT NULL,
pno SMALLINT NOT NULL,
qty SMALLINT, PRIMARY KEY (sno, pno))
To define the SNO column as the primary key of the SUPPLIER table, use a command like:
CREATE TABLE supplier(sno CHAR 3 NOT NULL,
sname CHAR 5, status SMALLINT,
city CHAR 6, PRIMARY KEY (sno))
A FOREIGN KEY constraint is used to enforce referential integrity -- the relationship between a referencing (or dependent) table and a referenced (or parent) table. The referential integrity rule requires that, for any value in the dependent columns, there must exist a row in the parent table such that the value of the dependent columns equals the value of the corresponding columns in the primary key of the parent table. This is enforced as follows:
Prevents record deletion from the parent table if dependents exist. RESTRICT is the default value.
Prevents records from being deleted from the parent table (as in the RESTRICT option), except the referential constraint is enforced at the end of the statement. This is the default setting if the CURRENT RULES special register is STD (SQL standard).
Deletes the dependent records from the current table and then deletes the parent records.
Causes each nullable column in the foreign key of each dependent record to be set to NULL before the corresponding parent records are deleted.
Note that whenever a cyclical situation exists (for example a foreign key of table B references the primary key of table A, and a foreign key of table A references a primary key of table B, the delete rules should be the same for all foreign keys, and the SET NULL option should not be used.
In order to create a referential integrity constraint you must create a FOREIGN KEY constraint on the referencing table. The FOREIGN KEY constraint must reference the existing PRIMARY KEY of the referenced table.
Foreign key columns do not need to have the same names as the corresponding columns in the PRIMARY KEY of the referenced table, but the columns must match in type, number and order.
Each column-name must be an unqualified name that identifies a column of the table except a LOB, ROWID, or security label column, and the same column must not be identified more than once. The number of identified columns must not exceed 64, and the sum of their length attributes must not exceed 255 minus the number of columns that allow null values. The referential constraint is a duplicate if the FOREIGN KEY and parent table are the same as the FOREIGN KEY and parent table of a previously defined referential constraint. The specification of a duplicate referential constraint is ignored with a warning.
A constraint name is used to uniquely identify a FOREIGN KEY constraint, and is stored in the appropriate system catalog table. The constraint name should be a short identifier (up to eight characters) that is unique among all constraints built on the table. Since this constraint name is also used in error messages to indicate constraint violation, it is important to use a meaningful name.
Although it is good practice to provide a meaningful constraint name, if one is not supplied, the system automatically generates one by taking the first eight characters of the first column (appearing in the column list) and then checking for uniqueness. If this name is not unique, the XDB Server takes the first seven characters of the first column name plus a number. If necessary one letter at a time is removed from the end of this constraint name and replaced with a number, until the constraint name becomes unique among all constraint names existing for the table.
The XDB Server automatically builds an index on your FOREIGN KEY columns. The system will generate the name of the index and store it in the REFKEYNAME column of the SYSIBM.SYSREF catalog table. DB2, in contrast, does not automatically create an index on FOREIGN KEY columns. If you are using your DB2 data definition language (DDL) script, and this script contains CREATE INDEX commands on FOREIGN KEY columns, you can end up with redundant indexes. For optimal performance, you should modify your DB2 DDL script to remove any CREATE INDEX commands on FOREIGN KEY columns.
The references portion of the referential-constraint clause is a separate clause. A single column can be identified as a foreign key of a referential constraint by using the references-clause in a column definition. Define a key that consists of more than one column by listing the column names to which the REFERENCES privilege applies.
Do not specify references-clause in the definition of a LOB, ROWID, or security label column; a LOB, ROWID, or security label column cannot be a foreign key.
A foreign key can reference a unique key or a primary key. When a foreign key refers to a nonprimary unique key, you must specify the columns of the key. If the foreign key includes a column defined as a distinct type, the corresponding column of the nominated parent key must be the same distinct type.
Indicates whether or not the referential constraint is enforced by DB2 during normal operations, such as insert, update, or delete.
Specifies that the referential constraint is enforced by the DB2 during normal operations (such as insert, update, or delete) and that it is guaranteed to be correct. This is the default.
Specifies that the referential constraint is not enforced by DB2 during normal operations, such as insert, update, or delete. This option should only be used when the data that is stored in the table is verified to conform to the constraint by some other method than relying on the database manager.
Specifies that the constraint can be used for query optimization. DB2 uses the information in query optimization using materialized query tables with the assumption that the constraint is correct. This is the default.
Specifies that the columns of the table have exactly the same name and description as the columns of the identified table or view. The name specified after LIKE must identify a table or view that exists at the current server, and the privilege set must implicitly or explicitly include the SELECT privilege on the identified table or view. An identified view must not include a column that is an explicitly defined ROWID column (including a distinct type that is based on a ROWID) or an identity column.
The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes all attributes of the n columns as they are described in SYSCOLUMNS with these exceptions:
The above defaults are chosen regardless of the current default of the base table column. Also, no column in the new table has a field procedure because the catalog descriptions of view columns do not include field procedures.
The implicit definition does not include any other attributes of the identified table or view. For example, the new table does not have a primary key or foreign key. 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.
Specifies whether identity column attributes are inherited from the definition of the source of the result table.
Specifies that identity column attributes are not inherited from the source result table definition. This is the default.
Specifies that, if available, identity column attributes (such as START WITH, INCREMENT BY, and CACHE values) are inherited from the definition of the source table. These attributes can be inherited if the element of the corresponding column in the table, view, or fullselect is the name of a column of a table or the name of a column of a view that directly or indirectly maps to the column name of a base table with the identity attribute. In other cases, the columns of the new temporary table do not inherit the identity attributes. The columns of the new table do not inherit the identity attributes in the following cases:
When a table has an identity column, DB2 can automatically generate unique, sequential numeric values for the column as rows are inserted into the table. Thus, identity columns are ideal for primary keys. Identity columns and ROWID columns are similar in that both types of columns contain values that DB2 generates and guarantees as unique. ROWID columns are used in large object (LOB) table spaces and can be useful in direct-row access. ROWID columns contain values of the ROWID data type, which returns a 40-byte VARCHAR value that is not regularly ascending or descending. ROWID data values are therefore not well suited to many application uses, such as generating employee numbers or product numbers. For data that is not LOB data and that does not require direct-row access, identity columns are usually a better approach, because identity columns contain existing numeric data types and can be used in a wide variety of uses for which ROWID values would not be suitable.
When a table is recovered to a point-in-time, it is possible that a large gap in the sequence of generated values for the identity column might result. For example, assume a table has an identity column that has an incremental value of 1 and that the last generated value at time T1 was 100 and DB2 subsequently generates values up to 1000. Now, assume that the table space is recovered back to time T1. The generated value of the identity column for the next row that is inserted after the recovery completes will be 1001, leaving a gap from 100 to 1001 in the values of the identity column.
Sometimes you may need to change the attributes of an identity column, For example, if you had defined an identity column with a data type of SMALLINT and then run out of assignable values, you need to redefine the column as INTEGER. To change the attributes of an identity column, you unload the data from the table, drop the table, recreate the table, and reload athe data.
But when you recreate the table, you must specify GENERATED BY DEFAULT and a new START WITH value for the identify column.Using GENERATED BY DEFAULT allows LOAD to reload the previously existing identity column values. You cannot use GENERATED ALWAYS in this case, but not using it is not a problem since DB2 always generates a value if a column value is not provided during insertion of an identity column defined as GENERATED BY DEFAULT. The new START WITH value should be the next value in the sequence from where the original sequence of values left off; this value is the next value that DB2 would generate first.
Indicates that the table cannot be dropped. Also, the database and table space that contain the table cannot be dropped.
If a table space is named, it must not be one that was created implicitly, be a partitioned table space that already contains a table, or be a LOB table space. If you name a partitioned table space, you cannot load or use the table until its partitioned index is created.
The CCSID clause is only syntactically supported for EBCDIC and ASCII. This is because the definition of the XDB location itself defines how character data is stored.
If you specify UNICODE, your character and graphic data is stored as UNICODE within the table.
Specifies how DB2 is to choose access to the table.
Specifies that index access should be used on this table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques are disabled when VOLATILE is used.
One instance in which use of VOLATILE may be desired is for a table whose size can vary greatly. If statistics are taken when the table is empty or has only a few rows, those statistics might not be appropriate when the table has many rows. Another instance in which use of VOLATILE may be desired is for a table that contains groups of rows, as defined by the primary key on the table. All but the last column of the primary key of such a table indicate the group to which a given row belongs. The last column of the primary key is the sequence number indicating the order in which the rows are to be read from the group. VOLATILE maximizes concurrency of operations on rows within each group, since rows are usually accessed in the same order for each operation.
Specifies that SQL access to this table should be based on the current statistics. NOT VOLATILE is the default.
An optional keyword that currently has no effect, but that is provided for DB2 family compatibility.
Specifies that the column definitions are based on the result of a fullselect. If materialized-query-table-options are specified, the table is a materialized query table and the REFRESH TABLE statement can be used to populate the table with the results of the fullselect.
Names the columns in the table. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result table of the fullselect.
A list of column names must be specified if the result table of the fullselect has duplicate column names or an unnamed column. 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.
Specifies that the table definition is based on the column definitions from the result of a query expression. The use of AS (fullselect) is an implicit definition of n columns for the table, where n is the number of columns that would result from the fullselect. The columns of the new table are defined by the columns that result from the fullselect. Every select list element must have a unique name. The AS clause can be used in the select-clause to provide unique names. The implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of fullselect.
If the fullselect is specified, for the statement that is embedded or prepared dynamically, the owner of the table being created must have the SELECT privilege on the tables or views referenced in the fullselect, or the privilege set must include SYSADM or DBADM authority for the database in which the tables of the fullselect reside. Having SELECT privilege means that the owner has at least one of the following authorizations:
The rules for establishing the qualifiers for names used in the fullselect are the same as the rules used to establish the qualifiers for table-name. The fullselect must not refer to host variables or include parameter markers.
When WITH NO DATA is specified, the fullselect must not:
When DISABLE QUERY OPTIMIZATION is specified, the following additional restrictions apply:
When a materialized query table is defined with ENABLE QUERY OPTIMIZATION specified, the following additional restrictions apply:
When fullselect does not satisfy the restrictions, an error occurs.
Specifies that the query is used only to define the attributes of the new a table. The table is not populated using the results of the query and the REFRESH TABLE statement cannot be used. When the WITH NO DATA clause is specified, the table is not considered a materialized query table.
The columns of the table are defined based on the definitions of the columns that result from the fullselect.
Specifies whether identity column attributes and column defaults are inherited from the definition of the source of the result table.
Specifies whether identity column attributes are inherited.
Specifies that identity column attributes are not inherited from the definition of the source of the result table. This is the default.
Specifies that, if available, identity column attributes (such as START WITH, INCREMENT BY, and CACHE values) are inherited from the definition of the source table. These attributes can be inherited if the element of the corresponding column in the table, view, or fullselect is the name of a column of a table or the name of a column of a view that directly or indirectly maps to the column name of a base table with the identity attribute. In other cases, the columns of the new temporary table do not inherit the identity attributes. The columns of the new table do not inherit the identity attributes in the following cases:
Specifies whether column defaults are inherited.
Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new table are either null or there are no default values. If the column can be null, the default is the null value. If the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on INSERT for the new table.
Specifies that column defaults for each updatable column of the definition of the source table are inherited. Columns that are not updatable do not have a default defined in the corresponding column of the created table.
Specifies that the default values for the table depend on data type of the columns that result from fullselect, as follows:
| Data type | Default value |
|---|---|
| Numeric | 0 |
| Fixed-length string | Blanks |
| Varying-length string | A string length of 0 |
| Date | CURRENT DATE |
| Time | CURRENT TIME |
| Timestamp | CURRENT TIMESTAMP |
Specifies the options for a refreshable materialized query table.
Specifies that the data is not inserted into the materialized query table when it is created. Use the REFRESH TABLE statement to populate the materialized query table, or use the INSERT statement to insert data into a user-maintained materialized query table.
Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated for a user-maintained materialized query table.
Specifies how the data in the materialized query table is maintained.
Specifies that the materialized query table is maintained by the system. Only the REFRESH statement is allowed on the table. This is the default.
Specifies that the materialized query table is maintained by the user, who can use the LOAD utility or the INSERT, DELETE, UPDATE, SELECT FOR UPDATE, or REFRESH TABLE statements on the table.
Specifies whether this materialized query table can be used for optimization.
Specifies that the materialized query table can be used for query optimization. If the fullselect specified does not satisfy the restrictions for query optimization, an error occurs. This is the default.
Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.
Names a table check constraint. If a constraint-name is not specified, a unique constraint name is derived from the name of the first column in the check-condition specified in the definition of the table check constraint.
Table check constraints can be defined on individual columns within the CREATE TABLE command. The check constraint is the check condition that defines the values that designated columns of the table can contain, with the following restrictions:
It must not contain any of the following:
For example, the CREATE TABLE command below builds the EMPLOYEE table with the following constraints:
CREATE TABLE EMPLOYEE ( e_no SMALLINT lname CHAR 10 CHECK (lname IS NOT NULL), fname CHAR 10 CHECK (fname IS NOT NULL), street CHAR 20, city CHAR 15, st CHAR 2, zip CHAR 5, dept CHAR 4 CHECK (dept='1050'), payrate MONEY, com DECIMAL(2,2), CONSTRAINT com CHECK (com <= 06) )
A table check constraint is not enforced on the default value of a column when the constraint is defined. A table check constraint is enforced on the default value of a column when rows are inserted or updated.
The syntax of a table check constraint is checked when the constraint is defined but the meaning is not checked. A table check constraint is not checked for consistency with other types of constraints.
Table check information is stored in two new catalog tables:
Contains one row for each table check constraint defined on a table.
Contains a row for each reference to a column in a table check constraint.
After table check constraints are defined on a table, any change must satisfy those constraints if it is made by an INSERT or UPDATE statement. A row satisfies a check constraint if its condition evaluates either to true or to unknown. A condition can evaluate to unknown for a row if one of the columns named contains the null value for that row. For INSERT or UPDATE statements, table check constraints are enforced on views and their underlying tables.
For populated tables, the enforcement of a table check constraint is determined by the value, STD or DB2, of the CURRENT RULES special register.
You can define a table column that has a default value other than null. When a row is inserted and no value is specified for the column, the default you defined is assigned to the column.
The default value depends on the data type of the column. The DEFAULT clause allows you to specify a default value in one of the following forms:
In a given column definition neither NOT NULL nor DEFAULT can be specified more than once. Both NOT NULL and DEFAULT cannot be specified. Omitting NOT NULL and DEFAULT implies that DEFAULT NULL is intended.
For example, if you want to record the identity of anyone inserting rows into a table, define the table with two additional columns:
| PRIMARY_ID | CHAR(8) | WITH DEFAULT USER, |
| SQL_ID | CHAR(8) | WITH DEFAULT CURRENT SQLID, |
You can allow updates and inserts to the table only through a view that omits those columns. Then, the primary authorization ID and the SQLID of the process are added by default.
The following shows how referential integrity can be used to enforce common business rules by defining relationships between the CUSTOMER, ORDERS, PRODUCTS, ITEMS, and EMPLOYEE tables in the TUTORIAL location.
All of the tables named already exist in the TUTORIAL location. However, if the tables did not already exist (or if you create them in another location), you could specify the referential integrity constraints at the same time you created the table.
The order in which the commands are executed is important. You cannot create a table that references another table until the referenced table is created. For example, the CUSTOMER table must be created before the ORDERS table.
Database Relationships
| Primary key: | customer(c_no) |
| Foreign key: | orders(o_no) |
| Delete rule: | RESTRICT |
Database Relationships
| Primary key: | employee(e_no) |
| Foreign key: | orders(s_no) |
| Delete rule: | SET NULL |
Database Relationships
| Primary key: | orders(o_no) |
| Foreign key: | items(o_no) |
| Delete rule: | CASCADE |
Database Relationships
| Primary key: | products(p_no) |
| Foreign key: | items(p_no) |
| Delete rule: | RESTRICT |
CREATE TABLE customer (c_no SMALLINT NOT NULL,
company CHAR(25), address CHAR(20), city CHAR(15),
state CHAR(2), zip CHAR(5), phone CHAR(14),
balance MONEY,
PRIMARY KEY (c_no));
CREATE UNIQUE INDEX custpkey ON customer(c_no)
CREATE TABLE employee (e_no SMALLINT NOT NULL,
lname CHAR(10), fname CHAR(10), street CHAR(20),
city CHAR(15), st CHAR(2), zip CHAR(5), dept CHAR(4),
payrate MONEY, com DECIMAL(2,2),PRIMARY KEY (e_no));
CREATE UNIQUE INDEX emppkey ON employee(e_no)
CREATE TABLE products (p_no SMALLINT NOT NULL,
description CHAR(18), price MONEY,PRIMARY KEY (p_no));
CREATE UNIQUE INDEX prodpkey ON products(p_no)
CREATE TABLE orders (o_no SMALLINT NOT NULL,
c_no SMALLINT, o_date DATE, s_no SMALLINT,
PRIMARY KEY (o_no),
FOREIGN KEY bad_cust (c_no)
REFERENCES customer ON DELETE RESTRICT,
FOREIGN KEY bad_emp (s_no)
REFERENCES employee ON DELETE SET NULL);
CREATE UNIQUE INDEX ordpkey ON orders(o_no)
CREATE TABLE items (o_no SMALLINT, p_no SMALLINT,
quantity SMALLINT, price MONEY,
FOREIGN KEY bad_ord(o_no)
REFERENCES orders ON DELETE CASCADE,
FOREIGN KEY bad_prod(p_no)
REFERENCES products ON DELETE CASCADE);
If the tables already exist you can specify the referential constraints using the ALTER TABLE command. This simply requires that you append the appropriate PRIMARY KEY and FOREIGN KEY clauses onto an ALTER TABLE table-name clause. The CREATE INDEX commands would remain the same, but should be issued before the ALTER TABLE ... PRIMARY KEY commands.
Examining the System Catalog
Creating, dropping or altering a table causes the system to automatically update the sysibm.systables and sysibm.syscolumns tables. You can query the system catalog tables for table, view, column and index information. The query results can be formatted into your own reports. See XDB Server System Tables for a description of the system catalog tables.
Optionally, you can use SQLWizard to examine table definitions interactively. In addition, the Catalog Browser function of SQLWizard lists table, view and index definitions.
If a column is indexed, the name(s) of the index or indexes are listed after the column name, separated by a slash (/). Index type indicator codes (see the following table) appear in parentheses after the index name.
| Indicator | Meaning |
|---|---|
| no letter | Regular index (duplicate values permitted) |
| p | Primary key |
| c | Candidate (Unique) key |
| f | Foreign key |
| u | Unique index |
| no number | Single column index |
| # | Compound index; # is sequence number indicating order of column in a compound index |