DB2 Universal Database V8.1 Certification Exam 700 Study Guide

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.

Related

Категории