DB2 - SQL Create Table Statement

The CREATE TABLE statement is used to create a new table in database.

Tables can be created in two ways:

1. By specifying the columns and their data types explicitly.

2. Creation based on the existing table.

Following is the table creation syntax by specifying the columns and their data types explicitly:

CREATE TABLE table-name
             column-definition
             table-constraint-clause
             physical-storage-clause

column-definition:
column-name data type
    [WITH DEFAULT expression]
    [NULL|NOT NULL]
    [column-constraint-clause]
    [, column-name data type [WITH DEFAULT expression] [NULL|NOT NULL]
    [column- constraint-clause]...]

Constraint (column-constraint-clause or table-constraint-clause)
[CONSTRAINT constraint-name]
    [REFERENCES table-name [(column-name)]
          [ON DELETE {RESTRICT | CASCADE| SET TO NULL}]
    [UNIQUE]
    [PRIMARY KEY]
    [CHECK (check-condition)]

physical-storage-clause
IN database-name.tablespace-name

Default:

• When a row is inserted into the table and no value is supplied for the column, the value specified in the default clause is inserted.
• If the expression is missed in the default clause, then the system defined default value for the data type of the column will be substituted.

NULL / NOT NULL:

• NULL is a default.
• NOT NULL: Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values.

Constraints:

• Column level:

  A column-level constraint refers to a single column and is defined together with the column. These are also referred to as “inline constraints”

• Table level :

  A table-level constraint references one or multiple columns and is defined separately after the definition of all the columns. These are called out-of-line constraints.

• You must use a table-level constraint if you are constraining more than one column.

Constraint-name:

• Names the constraint and is optional.

• If a constraint name is not specified, then a unique constraint name is generated.

• If the name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

FOREIGN KEY constraint

• The FOREIGN KEY constraint is defined with the REFERENCES keyword.

• The FOREIGN KEY constraint (referential integrity constraint), ensures that the values in the foreign key correspond to values of a primary key.

• When defining a FOREIGN KEY constraint on a table, the column name does not have to be identical to the column name it references.

• By default the foreign key constraint is of type DELETE RESTRICT: Parent rows cannot be deleted if child rows exist.

• ON DELETE CASCADE: Allows the deletion of the primary key row and also deletes the foreign key rows that relate to it.

• SET TO NULL: Allows the deletion of the primary key row and, instead of deleting all related foreign key rows, sets the foreign key columns to NULL.

UNIQUE constraint:

• To enforce unique values on an individual or a group of columns.

• UNIQUE constraint column should not contain NULL values.

• A table can contain one or more UNIQUE constraints.

PRIMARY KEY constraint:

• The PRIMARY KEY ensures all values in the column/columns are unique.

• The clause must not be specified more than once and the identified columns must be defined as NOT NULL.

Note: If you do not create a unique index for a primary key or for a unique constraint, then an incomplete key is defined for the table, making the table inaccessible.

Check:

Creating tables based on other tables.

Following is the table creation syntax based on the exiting table:

CREATE TABLE destination-table-name
      LIKE source-table-name
      IN database-name.tablespace-name

Example 1:

Create a table TB_TAB1 in database DB_DB1 and tablespace TS_TS1 with the following specifications with the column level constraints and with the implicit constraint names.

Let see how to code a CREATE TABLE statement below,

CREATE TABLE TB_TAB1
    (TAB1_COL1 INTEGER NOT NULL PRIMARY KEY,
      TAB1_COL2 INTEGER NOT NULL,
      TAB1_COL3 VARCHAR(5) REFERENCES ZIPCODE(ZIP)
          ON DELETE CASCADE,
      TAB1_COL4 DATE WITH DEFAULT,
      TAB1_COL5 CHAR(20) NOT NULL UNIQUE,
      TAB1_COL6 INTEGER CHECK(TAB1_COL6 < 100))
      IN DB_DB1.TS_TS1;

Explanation:

• TAB1_COL1 INTEGER NOT NULL PRIMARY KEY: When you define a column as Primary Key, it should be defined with “Not Null” (The primary key column should not contain Null values).

• TAB1_COL5 CHAR (20) NOT NULL UNIQUE: When you define a column with the unique constraint, it should be defined with “Not Null” (The unique constraint column should not contain Null values).

• TAB1_COL4 DATE WITH DEFAULT: During insert if you do not provide a value for this column, then the default value of “Current Date” for the “Date” variable will be inserted.

Example 2:

Create a table TB_TAB1 in database DB_DB1 and tablespace TS_TS1 with the following specifications with the table level constraints by naming the constraints exclusively.

Let see how to code a CREATE TABLE statement below,

CREATE TABLE TB_TAB1
      (TAB1_COL1 INTEGER NOT NULL,
      TAB1_COL2 INTEGER NOT NULL,
      TAB1_COL3 VARCHAR(5),
      TAB1_COL4 DATE WITH DEFAULT,
      TAB1_COL5 CHAR(20) NOT NULL,
      TAB1_COL6 INTEGER NOT NULL,
          CONSTRAINT TAB1_COL1_PK PRIMARY KEY(TAB1_COL1),
          CONSTRAINT TAB1_COL3_FK FOREIGN KEY(TAB1_COL3)
      REFERENCES ZIPCODE(ZIP),
          CONSTRAINT TAB1_COL5_COL6_UK UNIQUE(TAB1_COL5,TAB1_COL6),
          CONSTRAINT TAB1_COL6_CK CHECK(TAB1_COL6 < 100))
      IN DB_DB1.TS_TS1;

Explanation:

CONSTRAINT TAB1_COL5_COL6_UK UNIQUE(TAB1_COL5,TAB1_COL6): As the two columns TAB1_COL5 and TAB1_COL6 need to have unique constraints, you have combined and created this constraint and these two columns are defined with NOT NULL clause.

Example 3:

Create a table TB_TAB1 in a database DB_DB1 and in a table space TS_TS1, which exactly behaves like the table TB_TAB2.

CREATE TABLE TB_TAB1 LIKE TB_TAB2
        IN DB_DB1.TS_TS1;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!