APPX Software Library

Chapter 5: SQL Statements

5.4 CREATE TABLE

Description

Creates a table definition. A table definition consists of a list of column definitions that make up a table row. SQL provides two forms of the CREATE TABLE statement. The first form explicitly specifies column definitions. The second form, with the AS query_expression clause, implicitly defines the columns using the columns in the query expression.

Syntax

CREATE TABLE [ owner_name. ] table_name
    ( column_definition [ , { column_definition | table_constraint } ] ... )
    [ TABLE SPACE table_space_name ]
    [ PCTFREE number ]
    [ STORAGE_MANAGER ‘sto-mgr-id’ ]
    [ STORAGE_ATTRIBUTES ‘attributes’ ]
    ;

CREATE TABLE [ owner_name. ] table_name
    [ ( column_name [NULL | NOT NULL], ...) ]
    [ TABLE SPACE table_space_name ]
    [ PCTFREE number ]
    [ STORAGE_MANAGER ‘sto-mgr-id’ ]
    [ STORAGE_ATTRIBUTES ‘attributes’ ]
    AS query_expression
    ;

column_definition ::
    column_name data_type
    [ COLLATE collation_name ]
    [ DEFAULT { literal | USER | NULL | UID
       | SYSDATE | SYSTIME | SYSTIMESTAMP } ]
    [ column_constraint [ column_constraint ... ] ]

Arguments

owner_name

Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

table_name

Names the table definition. SQL defines the table in the database named in the last CONNECT statement.

column_name data_type

Names a column and associates a data type with it. The column names specified must be different than other column names in the table definition. The data_type must be one of the supported data types described in "Data Types" on page 4-4.

[ COLLATE collation_name ]

If data_type specifies a character column, the column definition can include an optional COLLATE clause. The COLLATE clause specifies a collation sequence supported by the underlying storage system. (See "Specifying the Character Set for Character Data Types" on page 4-6 for notes on character sets and collations, including the CASE_INSENSITIVE collation sequence supported for the default character set. See the documentation for your underlying storage system for details on any supported collations.)

DEFAULT

Specifies an explicit default value for a column. The column takes on the value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, the default value is NULL.

The DEFAULT clause accepts the following arguments:

literal

An integer, numeric or string constant.

USER

The name of the user issuing the INSERT or UPDATE statement on the table. Valid only for columns defined with character data types.

NULL

A null value.

UID

The user id of the user executing the INSERT or UPDATE statement on the table.

SYSDATE

The current date. Valid only for columns defined with DATE data types.

SYSTIME

The current time. Valid only for columns defined with TIME data types.

SYSTIMESTAMP column_constraint

The current date and time. Valid only for columns defined with TIMESTAMP data types.

Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see "Column Constraints".

table_constraint

Specifies a constraint that applies while inserting or updating a row in the table. For more information, see "Table Constraints".

TABLE SPACE table_space_name

Specifies the name of the table space where data stored in the table will reside. Table spaces provide a way to partition tables among different storage areas. In some storage systems, for instance, table spaces correspond to separate data files among which data in tables can be distributed. This arrangement can improve performance by distributing data in a table on different disk drives.

Different storage systems implement the concept of storage areas in different ways, if at all. So the actual behavior of the TABLE SPACE clause depends on the underlying storage system. See the documentation for your storage system for more details.

PCTFREE number

Specifies the desired percentage of free space for a table. The PCTFREE clause indicates to the storage system how much of the space allocated to a table should be left free to accommodate growth.

However, the actual behavior of the PCTFREE clause depends entirely on the underlying storage system. The SQL engine passes the PCTFREE value to the storage system, which may ignore it or interpret it. If the CREATE statement does not include a PCTFREE clause, the default is 20. See the documentation for your storage system for details.

STORAGE_MANAGER ‘sto-mgr-id’

A quoted string that identifies the storage system. The SQL engine uses the string to identify which storage system will create the table. If the CREATE TABLE statement omits the STORAGE_MANAGER clause, the SQL engine uses the string 'default'. What constitutes a valid name, and how the table is mapped to a specific storage system is defined by the implementation.

STORAGE_ATTRIBUTES ‘attributes’

A quoted string that specifies table attributes that are specific to a particular storage system. The SQL engine passes this string to the storage system, and its effects are defined by the storage manager. See the documentation for your storage system for details.

AS query_expression

Specifies a query expression to use for the data types and contents of the columns for the table. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the CREATE TABLE statement, column names are optional.

If omitted, the names for the table columns are also derived from the query expression. For more information, see "Query Expressions".

Examples

In the following example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the column itemno of the table john.item.

CREATE TABLE supplier_item (
  supp_no     INTEGER NOT NULL PRIMARY KEY,
  item_no     INTEGER NOT NULL REFERENCES john.item (itemno),
  qty         INTEGER
) ;

The following CREATE TABLE statement explicitly specifies a table owner, systpe:

CREATE TABLE systpe.account (
          account  integer,
          balance  money (12),
          info     char (84)
     ) ;

The following example shows the AS query_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:

CREATE TABLE systpe.dealer (name, street, city, state)
          AS
          SELECT name, street, city, state
          FROM customer
          WHERE customer.state IN ('CA','NY', 'TX') ;

The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:

CREATE TABLE emp (
     empno  integer NOT NULL,
     deptno  integer DEFAULT 10,
     join_date date DEFAULT NULL
) ;

Authorization

The user executing this statement must have either DBA or RESOURCE privilege. If the CREATE TABLE statement specifies a foreign key that references a table owned by a different user, the user must have the REFERENCES privilege on the corresponding columns of the referenced table.

The AS query_expression form of CREATE TABLE requires the user to have select privilege on all the tables and views named in the query expression.

SQL Compliance

SQL-92, ODBC Minimum SQL grammar. Extensions: TABLE SPACE, PCTFREE, STORAGE_MANAGER, and AS query_expression

Environment

Embedded SQL, interactive SQL, ODBC applications

DROP TABLE, Query Expressions

5.4.1 Column Constraints

Description

Specifies a constraint for a column that restricts the values that the column can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. SQL returns a Constraint violation error with SQLCODE of -20116.

Column constraints are similar to table constraints but their definitions are associated with a single column.

Syntax

column_constraint ::
      NOT NULL [ PRIMARY KEY | UNIQUE ]
    | REFERENCES [ owner_name. ] table_name [ ( column_name ) ]
    | CHECK ( search_condition )

Arguments

NOT NULL

Restricts values in the column to values that are not null.

NOT NULL PRIMARY KEY

Defines the column as the primary key for the table. There can be atmost one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint cannot contain null or duplicate values. Other tables can name primary keys as foreign keys in their REFERENCES clauses.

Other tables can name primary keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the primary key:

  • DROP TABLE statements that delete the table fail
  • DELETE and UPDATE statements that modify values in the column that match a foreign key's value also fail

The following example shows the creation of a primary key column on the table supplier.

CREATE TABLE supplier (
          supp_no     INTEGER NOT NULL PRIMARY KEY,
          name        CHAR (30),
          status      SMALLINT,
          city        CHAR (20)
     ) ;
NOT NULL UNIQUE

Defines the column as a unique key that cannot contain null or duplicate values. Columns with NOT NULL UNIQUE constraints defined for them are also called candidate keys.

Other tables can name unique keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the unique key:

  • DROP TABLE statements that delete the table fail
  • DELETE and UPDATE statements that modify values in the column that match a foreign key's value also fail

The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the table employee:

CREATE TABLE employee (
          empno        INTEGER NOT NULL PRIMARY KEY,
          ss_no        INTEGER NOT NULL UNIQUE,
          ename        CHAR (19),
          sal          NUMERIC (10, 2),
          deptno       INTEGER NOT NULL
     ) ;
REFERENCES table_name [ (column_name) ]

Defines the column as a foreign key and specifies a matching primary or unique key in another table. The REFERENCES clause names the matching primary or unique key.

A foreign key and its matching primary or unique key specify a referential constraint: A value stored in the foreign key must either be null or be equal to some value in the matching unique or primary key.

You can omit the column_name argument if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no.

CREATE TABLE orders (
  order_no INTEGER NOT NULL PRIMARY KEY,
  order_date DATE
) ;

CREATE TABLE order_item (
  orditem_order_no INTEGER REFERENCES orders ( order_no ),
  orditem_quantity INTEGER
) ;

Note that the second CREATE TABLE statement in the previous example could have omitted the column name order_no in the REFERENCES clause, since it refers to the primary key of table orders.

CHECK (search_condition)

Specifies a column-level check constraint. SQL restricts the form of the search condition. The search condition must not:

  • Refer to any column other than the one with which it is defined
  • Contain aggregate functions, subqueries, or parameter references

The following example creates a check constraint:

CREATE TABLE supplier (
     supp_no     INTEGER NOT NULL,
     name        CHAR (30),
     status      SMALLINT,
     city        CHAR (20) CHECK (supplier.city <> 'MOSCOW')
) ;

5.4.2 Table Constraints

Description

Specifies a constraint for a table that restricts the values that the table can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. SQL returns a Constraint violation error.

Table constraints have syntax and behavior similar to column constraints. Note the following differences:

  • The syntax for table constraints is separated from column definitions by commas.
  • Table constraints must follow the definition of columns they refer to.
  • Table constraint definitions can include more than one column and SQL evaluates the constraint based on the combination of values stored in all the columns.

Syntax

table_constraint ::
      PRIMARY KEY ( column [, ... ] )
    | UNIQUE ( column [, ... ] )
    | FOREIGN KEY ( column [, ... ] )
       REFERENCES [ owner_name. ] table_name [ ( column [, ... ] ) ]
    | CHECK ( search_condition )

Arguments

PRIMARY KEY ( column [, ... ] )

Defines the column list as the primary key for the table. There can be at most one primary key for a table.

All the columns that make up a table-level primary key must be defined as NOT NULL, or the CREATE TABLE statement fails. The combination of values in the columns that make up the primary key must be unique for each row in the table.

Other tables can name primary keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the primary key:

  • DROP TABLE statements that delete the table fail
  • DELETE and UPDATE statements that modify values in the combination of columns that match a foreign key's value also fail

The following example shows creation of a table-level primary key. Note that its definition is separated from the column definitions by a comma:

CREATE TABLE supplier_item (
     supp_no   INTEGER NOT NULL,
     item_no   INTEGER NOT NULL,
     qty       INTEGER NOT NULL DEFAULT 0,
          PRIMARY KEY (supp_no, item_no)
) ;
UNIQUE ( column [, ... ] )

Defines the column list as a unique, or candidate, key for the table. Unique key table-level constraints have the same rules as primary key table-level constraints, except that you can specify more than one UNIQUE table-level constraint in a table definition.

The following example shows creation of a table with two UNIQUE table-level constraints:

CREATE TABLE order_item (
     order_no    INTEGER NOT NULL,
     item_no     INTEGER NOT NULL,
     qty         INTEGER NOT NULL,
     price       MONEY NOT NULL,
          UNIQUE (order_no, item_no),
          UNIQUE (qty, price)
) ;
FOREIGN KEY ... REFERENCES

Defines the first column list as a foreign key and, in the REFERENCES clause, specifies a matching primary or unique key in another table.

A foreign key and its matching primary or unique key specify a referential constraint: The combination of values stored in the columns that make up a foreign key must either:

  • Have at least one of the column values be null
  • Be equal to some corresponding combination of values in the matching unique or primary key

You can omit the column list in the REFERENCES clause if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines the combination of columns student_courses.teacher and student_courses.course_title as a foreign key that references the primary key of the table courses. Note that the REFERENCES clause does not specify column names because the foreign key refers to the primary key of the courses table.

CREATE TABLE courses (
     teacher        CHAR (20) NOT NULL,
     course_title   CHAR (30) NOT NULL,
     PRIMARY KEY (teacher, course_title)
) ;

CREATE TABLE student_courses (
     student_id     INTEGER,
     teacher        CHAR (20),
     course_title   CHAR (30),
     FOREIGN KEY (teacher, course_title) REFERENCES courses
) ;

SQL evaluates the referential constraint to see if it satisfies the following search condition:

(student_courses.teacher IS NULL
  OR student_courses.course_title IS NULL)
OR
EXISTS (SELECT * FROM student_courses WHERE
     (student_courses.teacher = courses.teacher AND
      student_courses.course_title = courses.course_title)
        )

INSERT, UPDATE or DELETE statements that cause the search condition to be false violate the constraint, fail, and generate an error.

CHECK (search_condition)

Specifies a table-level check constraint. The syntax for table-level and column level check constraints is identical. Table-level check constraints must be separated by commas from surrounding column definitions.

SQL restricts the form of the search condition. The search condition must not:

  • Refer to any column other than columns that precede it in the table definition
  • Contain aggregate functions, subqueries, or parameter references

The following example creates a table with two column-level check constraints and one table-level check constraint:

CREATE TABLE supplier (
   supp_no   INTEGER NOT NULL,
   name      CHAR (30),
   status    SMALLINT CHECK (
         supplier.status BETWEEN 1 AND 100 ),
   city      CHAR (20) CHECK (
         supplier.city IN ('NEW YORK', 'BOSTON', 'CHICAGO')),
   CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)
) ;