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
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:
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 isNULL
.The
DEFAULT
clause accepts the following arguments:
An integer, numeric, or string constant. A null value. 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
EXAMPLESCREATE 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.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:
The following CREATE TABLE statement explicitly specifies a table owner, gus:
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:
The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:
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 |