9. Extended Statements : Create Table (extended) : Syntax
 
Share this page                  
Syntax
The CREATE TABLE statement has the following format:
[EXEC SQL] CREATE TABLE table_name
              [(column_specification {, column_specification }
               (column_specification {, column_specification }
              [, [CONSTRAINT constraint_name] table_constraint
              {, [CONSTRAINT constraint_name] table_constraint}])
              [WITH with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and load rows from another table) has the following format:
[EXEC SQL] CREATE TABLE table_name
              (column_name {, column_name}) AS
                            subselect
                            {UNION [ALL]
                            subselect}
              [WITH with_clause]
table_name
Specifies the name of the new table, and must be a valid object name (see Rules for Naming Objects).
column_specification
Specifies the characteristics of the column, as described in Column Specifications (see page Column Specifications).
subselect
Specifies a SELECT clause, described in detail in Select (see page Select) in this chapter.
table_constraint
Specifies a table constraint (see page Constraints), as one or more of the following:
UNIQUE (column_name {, column_name}) [WITH constraint_with_
clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})]
    [WITH constraint_with_clause]
WITH with_clause
Specifies WITH clause options.
For an overview of the Enterprise Access product WITH clause, see DBMS Extensions.
For valid WITH clause options for a specific Enterprise Access, see your Enterprise Access product guide.
For valid WITH clause options for the Ingres DBMS, see the SQL Reference Guide.