CREATE TABLE statement

Use the CREATE TABLE statement to create a new permanent table in the current database.

You can use the CREATE TABLE statement to create relational-database tables or to create typed tables (object-relational tables). For information about how to create temporary tables, see CREATE TEMP TABLE statement. For information about how to create external table objects that are not stored in the database, see CREATE EXTERNAL TABLE Statement.

Syntax


             .-STANDARD-.   (1)   
>>-CREATE--+-+----------+-+------------------------------------->
           '-+-----+------'       
             '-RAW-'              

>--TABLE--+---------------+--table------------------------------>
          '-IF NOT EXISTS-'          

        .-,-------------------------.  .-,------------------------------------------.        
        V                       (2) |  V                                            |        
>--+-(----| Column Definition |-----+----+----------------------------------------+-+--)-+-->
   |                                     |                                  (3)   |      |   
   |                                     '-+-| Multiple-Column Constraint |-----+-'      |   
   |                                       |                       (2)          |        |   
   |                                       '-| Column Definition |--------------'        |   
   |  (4)                     (5)                                                        |   
   '-------| OF TYPE Clause |------------------------------------------------------------'   

   .-,---------------------------------.   
   V                                   |   
>----+-------------------------------+-+------------------------>
     |  (4)                      (6) |     
     '-------WITH--+-AUDIT-----+-----'     
                   +-CRCOLS----+           
                   +-ERKEY-----+           
                   +-REPLCHECK-+           
                   '-VERCOLS---'           

>--+-------------------------+--+-------------------------------+-->
   |                     (7) |  |  (4)                      (8) |   
   '-| SECURITY Clause |-----'  '-------| Storage Options |-----'   

>--+-------------------------+--+-------------------------------+-->
   |                     (9) |  |                          (10) |   
   '-LOCK MODE--+-PAGE-+-----'  '-| USING Access-Method  |------'   
                '-ROW--'                                            

>--+-----------------------------------+-----------------------><
   |  (4)                         (11) |   
   '-------| Statistics Options |------'

Notes:

See Logging Options
See Column definition
See Multiple-Column Constraint Format
Informix® extension
See OF TYPE Clause
See Specialized columns
See SECURITY POLICY Clause
See Storage options
See LOCK MODE Options
See USING Access-Method Clause
See Statistics options of the CREATE TABLE statement

Element	Description	Restrictions	Syntax
table	Name that you declare here for the new table	Must be unique among the names of tables, synonyms, views, and sequences in the database	Identifier

Usage

When you create a table, you must declare its name and define its schema and its logging status. You can optionally specify additional attributes, as identified in topics that follow. The syntax diagram shows the sequence of required or optional specifications. These syntax segments of the CREATE TABLE statement, and some of their components, are identified in the five lists that follow.

The following keywords and clauses define column attributes of a new table:

Table 1. Defining the name, data-type, default value, and security label for a column
Specification	Topic	What the keyword or clause defines
Column Definition	Column definition	Column name and attributes, including data type, constraints, default value
DEFAULT	DEFAULT clause of CREATE TABLE	Default value for a column
COLUMN SECURED WITH	Column SECURED WITH label clause	An LBAC label for a protected column

The following keywords and clauses define constraints on the new table:

Table 2. Defining constraints on one or more columns of the table
Specification	Topic	What the keyword or clause defines
Single-Column Constraint	Single-Column Constraint Format	Data-integrity, referential, or other constraints on an individual column
Constraint Definition	Constraint Definition	Name, attributes, and enabled or disables status of constraints on the table
NULL	Using the NULL Constraint	Column allows NULL values
NOT NULL	Using the NOT NULL Constraint	Column does not allow NULL values
UNIQUE or DISTINCT	Using UNIQUE or DISTINCT Constraints	Column does not allow duplicate values
CHECK	CHECK Clause	Check constraints with other columns
PRIMARY KEY	Using the PRIMARY KEY Constraint	Contains a non-NULL unique value for each row in a table
FOREIGN KEY	Using the FOREIGN KEY Constraint	Establishes dependencies between tables
REFERENCES	REFERENCES Clause	Referential-integrity constraints with other columns
Multiple-Column Constraint	Multiple-Column Constraint Format	Data-integrity constraints on a set of columns

The following keywords and clauses define shadow columns and row-level audit support for the table:

Table 3. Defining shadow columns and row-level audit support
Specification	Topic	What the keyword or clause defines
WITH keyword	Specialized columns	Keyword options for shadow columns or for row-level audit support
WITH AUDIT	Using the WITH AUDIT Clause	Row-level audit support
WITH CRCOLS	Using the WITH CRCOLS Option	Keyword option for shadow columns or for row-level audit support
WITH ERKEY	Using the WITH ERKEY Keywords	3 shadow columns on which Enterprise Replication defines a primary key
WITH REPLCHECK	Using the WITH REPLCHECK Keywords	Shadow column that Enterprise Replication uses in consistency checking
WITH ROWIDS	Using the WITH ROWIDS Option	Hidden column in a fragmented table (deprecated)
WITH VERCOLS	Using the WITH VERCOLS Option	2 shadow columns for UPDATE operations on secondary servers

The following keywords and clauses define storage options for a new table:

Table 4. Defining storage for the table or for its smart-large-object columns
Specification	Topic	What the keyword or clause defines
Storage Options	Storage options	Where the table is physically stored and other information about how the table is stored
IN dbspace, sbspace, blobspace, or extspace	Using the IN Clause	Storage object to hold the new table (or part of it, or a large object)
FRAGMENT BY or PARTITION BY	FRAGMENT BY clause	Storage distribution scheme of a fragmented table
BY ROUND ROBIN	Fragmenting by ROUND ROBIN	A list of dbspaces for storing table fragments
BY EXPRESSION	Expression Fragment Clause	Expression-based fragment distribution
BY LIST	List fragment clause	List-based fragment distribution
BY RANGE . . . INTERVAL	Interval fragment clause	RANGE INTERVAL-based fragment distribution
PUT Clause	PUT Clause	Storage location, extent size, and other sbspace attributes for a BLOB or CLOB column
EXTENT SIZE	EXTENT SIZE Options	Sizes of the first and subsequent storage extents of the table
COMPRESSED	COMPRESSED option for tables	Whether automatic compression of large amounts of row data is enabled

The following keywords and clauses define the logging mode and additional table attributes, or insert into the new table the qualifying rows that a specified query returns.

Table 5. Logging options, locking granularity, access methods, typed table attributes, data distribution statistics options, data insertion from query results, or an LBAC security policy for the table.
Specification	Topic	What the keyword or clause defines
Logging Options (STANDARD or RAW)	Logging Options	Logging characteristics of the new table
LOCK MODE (PAGE or ROW)	LOCK MODE Options	Locking granularity of the new table
USING Access-Method	USING Access-Method Clause	How to access the new table
OF TYPE	OF TYPE Clause	Named ROW type of a typed table in an object-relational database
UNDER	Using the UNDER Clause	Supertable of a new subtable within a typed table hierarchy
SECURITY POLICY	SECURITY POLICY Clause	Label-based access control (LBAC) policy for the table
STATCHANGE, STATLEVEL	Statistics options of the CREATE TABLE statement	Change threshold and granularity of data distribution statistics
AS SELECT	AS SELECT clause	Creates and populates a query result table

Uniqueness rules for table names and column names

When you create a new table, every column must have a data type associated with it. The names of columns must be unique among the column in the same table. (The OF TYPE option specifies an existing named ROW type, whose fields provide column names and column data types for the typed table that you are creating.)

If the database was not created as MODE ANSI, the table name must be unique among all the identifiers of tables, views, sequence objects, and synonyms within the same database.

In an ANSI-compliant database, the combination owner.table must be unique among all the tables, synonyms, views, and sequence objects in the same database. Table objects qualified with different owner names can have the same identifier.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a table of the specified name is already registered in the current database.

Additional syntax notes for CREATE TABLE

For the restricted syntax options of CREATE TABLE statements that store the result set of a query in a new permanent table, see the AS SELECT clause.

In DB-Access, using CREATE TABLE outside the CREATE SCHEMA statement generates warnings if you use the -ansi flag or if the DBANSIWARN environment variable is set.

The order of table options

The syntax diagram shows the order of table options in CREATE TABLE statements that include more than one of the following options:

WITH options for specialized columns
SECURITY POLICY options
Storage options
LOCK MODE options
USING Access-Method clause
Statistics options

For example, the following two CREATE TABLE statements are equivalent:

CREATE STANDARD TABLE IF NOT EXISTS myShadowy_tab(colA INT, colB CHAR)
   WITH ERRKEY, WITH CRCOLS, WITH AUDIT LOCK MODE ROW;

CREATE STANDARD TABLE IF NOT EXISTS myShadowy_tab(colA INT, colB CHAR)
   WITH AUDIT, WITH ERRKEY, WITH CRCOLS LOCK MODE ROW;

If you issue both statements consecutively in the same database, the second statement fails, because the table called myShadowy_tab that the first statement created already exists in the database. Because of the IF NOT EXISTS keywords, the redundant second statement returns no error, but it creates no new table.

The following example fails with an error, because no other Options clause can precede a WITH clause:

CREATE TABLE shadow_columns (colA INT, colB CHAR)
   LOCK MODE ROW WITH AUDIT, WITH ERRKEY, WITH CRCOLS; --incorrect options order

The next CREATE TABLE example also fails, because the Statistics option cannot precede the LOCK MODE option within the same Options clause:

CREATE TABLE shadow_columns (colA INT, colB CHAR)
   STATCHANGE 25 STATLEVEL TABLE LOCK MODE PAGE; --bad options order