2.10 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

[ 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 Section 1.2 "Data Types" on page 1-3.

[ 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.

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	The current date and time. Valid only for columns defined with TIMESTAMP data types.

column_constraint
Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see Section 2.10.1 "Column Constraints" on page 2-17.

table_constraint
Specifies a constraint that applies while inserting or updating a row in the table. For more information, see Section 2.10.2 "Table Constraints" on page 2-19.

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 Section 1.2.6 "Query Expressions" on page 1-9.

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, admin:

CREATE TABLE admin.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 admin.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
Related Statements	DROP TABLE, Query Expressions