SQL Reference Guide

Previous Topic

Next Topic



Creates a table definition. A table definition consists of a list of column definitions that make up a table row. FairCom DB SQL provides two forms of the CREATE TABLE statement. The first form explicitly specifies column definitions. The second form, with the AS query_expression clause, implicitly defines the columns using the columns in the query expression.


CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

( column_definition [ , { column_definition | table_constraint } ] ... )

[ STORAGE_ATTRIBUTES 'attributes' ]


CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

[ ( column_name [NULL | NOT NULL], ... ) ]

[ STORAGE_ATTRIBUTES 'attributes' ]

AS query_expression


column_definition ::

column_name data_type

[ DEFAULT { literal | USER | NULL | UID


| [ IDENTITY [(seed, increment)] | auto_increment [(seed, increment)] ]

[ column_constraint [ column_constraint ... ] ]



This argument avoids failure by creating the table only if a table of the same name does not already exist.


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.


Names the table definition. FairCom DB SQL defines the table in the database named in the last CONNECT statement.

column_name data_type

Names a column and associates a data type with it. The column names specified must be different than other column names in the table definition. The data_type must be one of the supported data types described in Data Types.


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:


An integer, numeric or string constant.


The name of the user issuing the INSERT or UPDATE statement on the table. Valid only for columns defined with character data types.


A null value.


The user id of the user executing the INSERT or UPDATE statement on the table.


The current date. Valid only for columns defined with DATE data types.


The current time. Valid only for columns defined with TIME data types.


The current date and time. Valid only for columns defined with TIMESTAMP data types.


Specifies that the column is an IDENTITY column. An IDENTITY column is one for which the data is not provided by the user but it is automatically generated by the DBMS. There can be only one identity column for a table. A default value cannot be specified for an identity column. IDENTITY can only be specified on columns of type INTEGER, SMALLINT, TINTYINT and BIGINT.


Specifies a value that used for the identity column when the first row is inserted into the table. Specifying a seed value is optional. If a seed value is not specified, then it defaults to 1.


Specifies a value by which the identity value of the previous row is incremented, which is then used for the identity column when the second and subsequent rows are inserted into the table. Specifying the increment value is optional. If it is not specified, then it defaults to 1.

Note: Although both the seed and increment values are optional, they must both be specified or both be left unspecified. It is not possible to specify only one of them.


auto_increment is specified and behaves exactly like IDENTITY, however, values can be automatically overridden from the INSERT statement. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically. This provides flexibility many ORM provides such as SQL Alchemy depend on.


Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see Column Constraints.


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


Specifies a string describing underlying FairCom DB table storage attributes. For more information see Storage Attributes.

AS query_expression

Specifies a query expression to use for the data types and contents of the columns for the table. 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 for the table columns are also derived from the query expression. For more information, see Query Expressions.


In the following example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the column “itemno” of the table “john.item”.

CREATE TABLE supplier_item (


item_no INTEGER NOT NULL REFERENCES john.item (itemno),


) ;

The following CREATE TABLE statement explicitly specifies a table owner, admin:

CREATE TABLE admin.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 (name, street, city, state)


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:


empno integer NOT NULL,

deptno integer DEFAULT 10,

join_date date DEFAULT NULL

) ;


The user executing this statement must have either DBA or RESOURCE privilege. If the CREATE TABLE statement specifies a foreign key that references a table owned by a different user, the user must have the REFERENCES privilege on the corresponding columns of the referenced table.

The AS query_expression form of CREATE TABLE requires the user to have select privilege on all the tables and views named in the query expression.

SQL Compliance

SQL-92, ODBC Minimum SQL grammar. Extensions: AS query_expression


Embedded SQL, interactive SQL, ODBC applications

Related Statements

DROP TABLE, ALTER TABLE, Query Expressions

See Also: