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

(explicit id cret004) cret004 (explicit id cret005) cret005 (explicit id cret007) cret007 (explicit id cret009) cret009 (explicit id cret010) cret010 (explicit id opt003) opt003 (explicit id opt004) opt004 (explicit id opt005) opt005 (explicit id opt006) opt006 (explicit id opt007) opt007 (explicit id opt008) opt008 CREATE { STANDARD | [RAW] } [] TABLE [IF NOT EXISTS] table { ( <Column Definition> [] [ { <Multiple-Column Constraint> [] | <Column Definition> [] } ] ) | [] <OF TYPE Clause> [] } [ [] WITH { AUDIT | CRCOLS | ERKEY | REPLCHECK | VERCOLS } [] ] [ <SECURITY Clause> [] ] [ [] <Storage Options> [] ] [ LOCK MODE { PAGE | ROW } [] ] [ <USING Access-Method > [] ] [ [] <Statistics Options> [] ]
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 ids_sqs_2095.html#ids_sqs_2095 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
1 See Column definition
2 See Multiple-Column Constraint Format
3 Informix® extension
4 See OF TYPE Clause
5 See Specialized columns
6 See Storage options
7 See LOCK MODE Options
8 See USING Access-Method Clause
9 See SECURITY POLICY Clause
10 See Statistics options of the CREATE TABLE statement
11 See Logging Options