Creating Tables with the CREATE TABLE SQL Statement

Creating Tables with the CREATE TABLE SQL Statement

In Chapter 4, we saw that a table is a logical database object that acts as the main repository in a database. We also saw that there are two ways a table can be created: by using the Create Table Wizard provided with the Control Center and by using the CREATE TABLE SQL statement.

Up until now, we've seen how to use a relatively simple form of the CREATE TABLE statement to construct very basic tables. Yet, the CREATE TABLE is probably the most complex SQL statement available (in fact, over 60 pages of the DB2 UDB SQL Reference manual are devoted to this statement alone). And because this statement is so complex, its syntax can be quite intimidating. Fortunately, you do not have to know all the nuances of the CREATE TABLE statement to pass the DB2 UDB V8.1 Family Fundamentals certification exam (Exam 700). Still, you do need to know the basics, and the remainder of this chapter is devoted to the CREATE TABLE statement and to the syntax you must be familiar with. With that said, let's begin by taking a look at the simplest form of the CREATE TABLE SQL statement.

In its simplest form, the syntax for the CREATE TABLE SQL statement is:

 CREATE TABLE [  TableName  ] ( [  Element  ] ,...) <IN [  TablespaceName  ]> <INDEX IN [  TablespaceName  ]> <LONG IN [  TablespaceName  ]> 

where:

TableName	Identifies the name to be assigned to the table to be created. (A table name must be unique within the schema the table is to be defined in.)
Element	Identifies one or more columns , unique/primary key constraints, referential constraints, and/or check constraints to be included in the table definition. The syntax used for defining each of these elements varies according to the element being defined.
TablespaceName	Identifies the tablespace that the table and its regular data, indexes, and/or long data/large object data is to be stored in. (Regular data, indexes, and long/large object data can only be stored in separate tablespaces if DMS tablespaces are used.)

The basic syntax used to define a column is:

 [  ColumnName  ] [  DataType  ] <NOT NULL> <WITH DEFAULT <[  DefaultValue  ]  CURRENT DATE      CURRENT TIME  CURRENT TIMESTAMP>> <  UniqueConstraint  > <  CheckConstraint  > <  ReferentialConstraint  > 

where:

ColumnName	Identifies the unique name to be assigned to the column to be created.
DataType	Identifies the data type (built-in or user -defined) to be assigned to the column to be created; the data type specified determines the kind of data values that can be stored in the column. (Table 6-4 contains a list of the data type definitions that are valid).
DefaultValue	Identifies the value to be provided for the column in the event no value is supplied for the column when an insert or update operation is performed against the table.
UniqueConstraint	Identifies a unique or primary key constraint to be associated with the column.
CheckConsraint	Identifies a check constraint to be associated with the column.
ReferentialConstraint	Identifies a referential constraint to be associated with the column.

Table 6-4. Data Type Definitions That Can Be Used with the CREATE TABLE Statement

Data Type	Definition(s)
Small Integer	SMALLINT
Integer	INTEGER INT
Big Integer	BIGINT
Decimal	DECIMAL( Precision, Scale ) DEC( Precision, Scale ) NUMERIC( Precision, Scale ) NUM( Precision, Scale ) where Precision is any number between 1 and 31; Scale is any number between 0 and Precision
Single-Precision Floating-Point	REAL FLOAT( Precision ) where Precision is any number between 1 and 24
Double-Precision Floating-Point	DOUBLE FLOAT( Precision ) where Precision is any number between 25 and 53
Fixed-Length Character String	CHARACTER( Length ) <FOR BIT DATA> [*] CHAR( Length ) <FOR BIT DATA> [*] where Length is any number between 1 and 254
Varying-Length Character String	CHARACTER VARYING( MaxLength ) <FOR BIT DATA> [*] CHAR VARYING( MaxLength ) <FOR BIT DATA> [*] VARCHAR( MaxLength ) <FOR BIT DATA> [*] where MaxLength is any number between 1 and 32,672
Long Varying-Length Character String	LONG VARCHAR
Fixed-Length Double-Byte Character String	GRAPHIC( Length ) where Length is any number between 1 and 127
Varying-Length Double-Byte Character String	VARGRAPHIC( MaxLength ) where MaxLength is any number between 1 and 16,336
Long Varying-Length Double-Byte Character String	LONG VARGRAPHIC
Date	DATE
Time	TIME
Timestamp	TIMESTAMP
Binary Large Object	BINARY LARGE OBJECT( Size <K M G>) BLOB( Size <K M G>) where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.
Character Large Object	CHARACTER LARGE OBJECT( Size <K M G>) CHAR LARGE OBJECT( Size <K M G>) CLOB( Size <K M G>) where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.
Double-Byte Character Large Object	DBCLOB( Size <K M G>) where Length is any number between 1 and 1,073,741,823; if K (for kilobyte) is specified, Length is any number between 1 and 1,048,576; if M (for megabyte) is specified, Length is any number between 1 and 1,024; if G (for gigabyte) is specified, Length is must be 1.

^[*] If the FOR BIT DATA option is used with any character string data type definition, the contents of the column the data type is assigned to are treated as binary data. As a result, code page conversions are not performed if data is exchanged between other systems, and all comparisons made are done in binary, regardless of the collating sequence used by the database.

The syntax used to create a unique or primary key constraint as part of a column definition is:

 <CONSTRAINT [  ConstraintName  ]> [UNIQUE  PRIMARY KEY] 

where:

ConstraintName

Identifies the unique name to be assigned to the constraint to be created.

The syntax used to create a check constraint as part of a column definition is:

 <CONSTRAINT [  ConstraintName  ]> CHECK ( [  CheckCondition  ] ) <ENFORCED  NOT ENFORCED> <ENABLE QUERY OPTIMIZATION  DISABLE QUERY OPTIMIZATION> 

where:

ConstraintName	Identifies the unique name to be assigned to the constraint to be created.
CheckCondition	Identifies a condition or test that must evaluate to TRUE before the value provided for the column will actually be stored in the table.

And finally, the syntax used to create a referential constraint as part of a column definition is:

 <CONSTRAINT [  ConstraintName  ]> REFERENCES [  PKTableName  ] < ( [  PKColumnName  ] ,...) > <ON UPDATE [NO ACTION  RESTRICT]> <ON DELETE [CASCADE  SET NULL  NO ACTION  RESTRICT]> <ENFORCED  NOT ENFORCED> <ENABLE QUERY OPTIMIZATION  DISABLE QUERY OPTIMIZATION> 

where:

ConstraintName	Identifies the unique name to be assigned to the constraint to be created.
PKTableName	Identifies the name of the parent table that is to participate in the referential constraint.
PKColumnName	Identifies the column(s) that make up the parent key of the parent table that is to participate in the referential constraint.

Thus, if you wanted to create a table that had three columns in it, two of which use an integer data type and another that uses a fixed-length character string data type, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE EMPLOYEES     (EMPID  INTEGER,      NAME   CHAR(50)      DEPT   INTEGER) 

If you wanted to create the same table such that the EMPID column had both the NOT NULL constraint and a unique constraint associated with it, you could do so by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE EMPLOYEES     (EMPID  INTEGER NOT NULL PRIMARY KEY,      NAME   CHAR(50)      DEPT   INTEGER) 

And if you wanted to create the same table such that the DEPT column participates in a referential constraint with the DEPARTMENT table, you could do so by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE EMPLOYEES     (EMPID  INTEGER,      NAME   CHAR(50)      DEPT   INTEGER REFERENCES DEPARTMENT (DEPTID)) 

As you can see, a unique constraint, a check constraint, and/or a referential constraint that involves a single column can be defined as part of that particular column's definition. But what if you needed to define a constraint that encompasses multiple columns in the table? You do this by defining a constraint as another element, rather than as an extension to a single column's definition. The basic syntax used to define a unique constraint as an individual element is:

 <CONSTRAINT [  ConstraintName  ]> [UNIQUE  PRIMARY KEY] ( [  ColumnName  ] ,...) 

where:

ConstraintName	Identifies the unique name to be assigned to the constraint to be created.
ColumName	Identifies one or more columns that are to be part of the unique or primary key constraint to be created.

The syntax used to create a check constraint as an individual element is the same as the syntax used to create a check constraint as part of a column definition:

 <CONSTRAINT [  ConstraintName  ]> CHECK ( [  CheckCondition  ] ) <ENFORCED  NOT ENFORCED> <ENABLE QUERY OPTIMIZATION  DISABLE QUERY OPTIMIZATION> 

where:

ConstraintName	Identifies the unique name to be assigned to the constraint to be created.
CheckCondition	Identifies a condition or test that must evaluate to TRUE before the value provided for the column will actually be stored in the table.

And finally, the syntax used to create a referential constraint as an individual element is:

 <CONSTRAINT [  ConstraintName  ]> FOREIGN KEY ( [  ColumnName  ] ,...) REFERENCES [  PKTableName  ] < ( [  PKColumnName  ] ,...) > <ON UPDATE [NO ACTION  RESTRICT]> <ON DELETE [CASCADE  SET NULL  NO ACTION  RESTRICT]> <ENFORCED  NOT ENFORCED> <ENABLE QUERY OPTIMIZATION  DISABLE QUERY OPTIMIZATION> 

where:

ConstraintName	Identifies the unique name to be assigned to the constraint to be created.
ColumnName	Identifies one or more columns that are to be part of the referential constraint to be created.
PKTableName	Identifies the name of the parent table that is to participate in the referential constraint.
PKColumnName	Identifies the column(s) that make up the parent key of the parent table that is to participate in the referential constraint.

Thus, a table that was created by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE EMPLOYEES     (EMPID  INTEGER NOT NULL PRIMARY KEY,      NAME   CHAR(50)      DEPT   INTEGER REFERENCES DEPARTMENT (DEPTID)) 

could also be created by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE EMPLOYEES     (EMPID  INTEGER NOT NULL,      NAME   CHAR(50)      DEPT   INTEGER,      PRIMARY KEY (EMPID),      FOREIGN KEY (DEPT) REFERENCES DEPARTMENT (DEPTID)) 

Creating Tables That Are Similar to Existing Tables

At times, it may be desirable to create a new table that has the same definition as an existing table. To perform such an operation, you could execute a CREATE TABLE statement that looks identical to the CREATE TABLE statement used to define the original table. Or better still, you could use a special form of the CREATE TABLE statement. The syntax for this form of the CREATE TABLE is:

 CREATE TABLE [  TableName  ] LIKE [  SourceTable  ] <[INCLUDING  EXCLUDING] COLUMN DEFAULTS> <[INCLUDING  EXCLUDING] IDENTITY COLUMN ATTRIBUTES> 

where:

TableName	Identifies the unique name to be assigned to the table to be created.
SourceTable	Identifies the name of an existing table whose structure is to be used to define the table to be created.

When this form of the CREATE TABLE is executed, the table that is ultimately created will have the same number of columns as the source table specified, and these columns will have the same names , data types, and nullability characteristics as those of the source table. In addition, unless the EXCLUDING COLUMN DEFAULTS option is specified, any default constraints defined for columns in the source table will be copied to the new table as well. However, no other attributes of the source table will be duplicated . Thus, the table that is created will not contain unique constraints, referential constraints, triggers, or indexes that have been defined for the source table used.

A Word about Declared Temporary Tables

Before we look at some more complex examples of the CREATE TABLE statement, another type of table that is commonly used should be mentioned. This type of table is known as a declared temporary table. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them ”and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the description of the table is dropped. Whereas base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement.