Progress
SQL-92
Guide and Reference


CREATE TABLE Statement

Creates a table definition. A table definition consists of a set of named column definitions for data values that will be stored in rows of the table. SQL provides two forms of the CREATE TABLE statement.

The first syntax form explicitly specifies column definitions. The second syntax form, with the AS query_expression clause, implicitly defines the columns using the columns in a query expression.

SYNTAX

CREATE TABLE [ owner_name.]table_name 
  ( { column_definition | table_constraint }, ...) 
    [ AREA area_name ]  
    ; 
CREATE TABLE [ owner_name.]table_name 
    [ (column_name [ NOT NULL] , ... ) ] 
    [ AREA area_name ] 
    AS query_expression 
    ; 

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 you are defining.

column_definition:

SYNTAX
column_name   data_type 
  [ DEFAULT { literal | NULL | SYSDATE } ] 
  [ column_constraint [ column_constraint , ... ] ] 

column_name data_type

Names a column and associates a data type with it. The column names specified must be different from other column names in the table definition. The data_type must be one of the supported data types described in the "Data Types" section in SQL-92 Language Elements."

Note that when a table contains more than one column, a comma separator is required after each column_definition except for the final column_definition.

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.
NULL 
A null value.
SYSDATE 
The current date. Valid only for columns defined with DATE data types. SYSDATE is equivalent to the Progress default keyword TODAY.

column_constraint

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

table_constraint

Specifies a constraint that will be applied while inserting or updating a row in the table. For more information, see the Table Constraints section.

AREA area_name

Specifies the name of the storage area where data stored in the table is to be stored.

If the specified area does not exist, the database returns an error. If you do not specify an area, the default area is used.

AS query_expression

Specifies a query expression to use for the data types and data values of the table's columns. 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 of the table's columns are taken from the column names of the query expression.

EXAMPLES

In the following CREATE TABLE supplier_item example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the itemno column 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, gus:

CREATE TABLE gus.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 gus.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

Must have DBA privilege, RESOURCE privilege or SELECT privilege.

SQL COMPLIANCE

SQL-92, ODBC Minimum SQL grammar, Progress Extensions: AREA and AS query_expression

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications, JDBC applications

RELATED STATEMENTS

DROP TABLE Statement, "Query Expressions" in "SQL-92 Language Elements"


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095