Icon CREATE TABLE

Creates a new table.

Syntax
CREATE [TEMPORARY] TABLE <Name>
[[(
<ColumnName> <ColumnDefinition>|
LIKE <LikeDefinition>|
[CONSTRAINT <ConstraintName>] <ConstraintDefinition>]
[,<ColumnName> <ColumnDefinition>|
LIKE <LikeDefinition>|
[CONSTRAINT <ConstraintName>] <ConstraintDefinition>]
)]
[AS <QueryExpression> WITH DATA|WITH NO DATA]
[FROM PUBLISHED UPDATES [TABLES <TableName> [,<TableName>]]] | [FROM UPDATES <UpdateName> IN STORE <StoreName>]]
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[READONLY|READWRITE]
[ENCRYPTED|UNENCRYPTED]
[INDEX PAGE SIZE <IndexPageSize>]
[BLOB BLOCK SIZE <BLOBBlockSize>]
[PUBLISH BLOCK SIZE <PublishBlockSize>]
[PUBLISH COMPREESSION <Compression>]
[MAX ROW BUFFER SIZE <MaxRowBufferSize>]
[MAX INDEX BUFFER SIZE <MaxIndexBufferSize>]
[MAX BLOB BUFFER SIZE <MaxBLOBBufferSize>]
[MAX PUBLISH BUFFER SIZE <MaxPublishBufferSize>]
[ATTRIBUTES <CustomAttributes>]

<ColumnDefinition> = 

<DataType>
[COMPRESSION <Compression>]
[GENERATED <GenerationOptions>|COMPUTED <ComputationOptions>|
DEFAULT <DefaultExpression>]
[NOT NULL [ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]]
[<ColumnConstraintDefinition>] [<ColumnConstraintDefinition>]
[DESCRIPTION <Description>]

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<Compression> = 0..9

<GenerationOptions> =
ALWAYS AS <GenerateExpression>|
ALWAYS AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)|
BY DEFAULT AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)

<GenerateExpression> =
Any valid SQL expression that does not include any sub-queries

<ComputationOptions> =
ALWAYS AS <ComputeExpression>

<ComputeExpression> =
Any valid SQL expression that does not include any sub-queries
or user-defined function references

<DefaultExpression> =
Any valid SQL expression that does not include any sub-queries

<ColumnConstraintDefinition> =

CHECK <CheckExpression>
PRIMARY KEY|
UNIQUE|
REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<LikeDefinition> =

<TableName>
[INCLUDING|EXCLUDING IDENTITY]
[INCLUDING|EXCLUDING DEFAULTS]
[INCLUDING|EXCLUDING GENERATED]

<ConstraintDefinition> = 

CHECK <CheckExpression>
PRIMARY KEY (<ColumnName> [,<ColumnName>])|
UNIQUE (<ColumnName> [,<ColumnName>])|
FOREIGN KEY (<ColumnName> [,<ColumnName>])
   REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]
[DESCRIPTION <Description>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

Usage
Use this statement to create a new table. Use the TEMPORARY clause to specify that the table should be created as a local temporary table that is only visible to the current session. You may only use the following DDL statements on temporary tables:

   DROP TABLE
   RENAME TABLE
   EMPTY TABLE
   CREATE INDEX
   CREATE TEXT INDEX
   DROP INDEX
   RENAME INDEX

Use the READONLY clause to specify that a table is always read-only. Doing so can improve multi-user performance on a table because ElevateDB will not need to perform any locking on such a table.

FROM PUBLISHED UPDATES and FROM UPDATES Clauses

Use the FROM PUBLISHED UPDATES or FROM UPDATES version of the CREATE TABLE statement to create a table that contains the contents of pending published updates that have not been saved, or the contents of an existing replication update file in a store. These two clauses are mutually-exclusive, and only one can be used at a time.

The format of the created table will be as follows:

"TableName" VARCHAR(40) COLLATE "ANSI_CI",
"UpdateType" VARCHAR(15) COLLATE "ANSI_CI",
"UpdateTimeStamp" TIMESTAMP,
"Manifest" CLOB COLLATE "ANSI_CI",
"KeyData" CLOB COLLATE "ANSI_CI",
"RowData" CLOB COLLATE "ANSI_CI"

The UpdateType column will contain one of the following values:

UpdateTypeDescription
InsertThe update is an insert operation. The RowData column will contain a CRLF-delimited list of column:value pairs.
UpdateThe update is an update operation. The KeyData column will contain a CRLF-delimited list of primary key column:value pairs, and the RowData column will contain a CRLF-delimited list of column:value pairs.
DeleteThe update is an update operation. The KeyData column will contain a CRLF-delimited list of primary key column:value pairs.

The Manifest column contains a CRLF-delimited list of published table IDs that serve to tell ElevateDB which published tables have loaded this update already.

With the PUBLISHED UPDATES clause, you can further limit the published tables that are used to generate the table by using the TABLES clause. When using the TABLES clause, only the pending published updates for the specified tables will be included in the created table.

For more information on replication, please see the Replication topic.

Examples
-- The following statement creates the Customer table

CREATE TABLE "Customer"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
"Name" VARCHAR(30) COLLATE "ANSI_CI",
"Address1" VARCHAR(40) COLLATE "ANSI_CI",
"Address2" VARCHAR(40) COLLATE "ANSI_CI",
"City" VARCHAR(30) COLLATE "ANSI_CI",
"State" CHAR(2) COLLATE "ANSI_CI",
"Zip" CHAR(10) COLLATE "ANSI_CI",
"CreatedOn" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ID_PrimaryKey" PRIMARY KEY ("ID"),
CONSTRAINT "ID_Check" CHECK (ID IS NOT NULL),
CONSTRAINT "Name_Check" CHECK (Name IS NOT NULL)
)

-- The following statement creates a temporary table containing
-- the contents of an update file

CREATE TEMPORARY TABLE "MainOfficeUpdates"
FROM UPDATES "MainOffice-2010-12-14 17-57-12.0306"
IN STORE MainUpdates

Required Privileges
The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
Column COMPUTEDThe column COMPUTED clause is an ElevateDB extension.
Column ERROR CODE/MESSAGEThe column ERROR CODE/MESSAGE clause is an ElevateDB extension.
Column DESCRIPTIONThe column DESCRIPTION clause is an ElevateDB extension.
Column COMPRESSIONThe column COMPRESSION clause is an ElevateDB extension.
Constraint ON UPDATEThe only option supported for the ON UPDATE clause is the RESTRICT option.
Constraint ON DELETEThe only option supported for the ON DELETE clause is the RESTRICT option.
Constraint ERROR CODE/MESSAGEThe constraint ERROR CODE/MESSAGE clause is an ElevateDB extension.
Constraint DESCRIPTIONThe constraint DESCRIPTION clause is an ElevateDB extension.
DESCRIPTIONThe DESCRIPTION clause is an ElevateDB extension.
VERSIONThe VERSION clause is an ElevateDB extension.
ENCRYPTEDThe ENCRYPTED and UNENCRYPTED clauses are an ElevateDB extension.
INDEX PAGE SIZEThe INDEX PAGE SIZE clause is an ElevateDB extension.
BLOB BLOCK SIZEThe BLOB BLOCK SIZE clause is an ElevateDB extension.
PUBLISH BLOCK SIZEThe PUBLISH BLOCK SIZE clause is an ElevateDB extension.
PUBLISH COMPRESSIONThe PUBLISH COMPRESSION clause is an ElevateDB extension.
MAX ROW BUFFER SIZEThe MAX ROW BUFFER SIZE clause is an ElevateDB extension.
MAX INDEX BUFFER SIZEThe MAX INDEX BUFFER SIZE clause is an ElevateDB extension.
MAX BLOB BUFFER SIZEThe MAX BLOB BUFFER SIZE clause is an ElevateDB extension.
MAX PUBLISH BUFFER SIZEThe MAX PUBLISH BUFFER SIZE clause is an ElevateDB extension.
ATTRIBUTESThe ATTRIBUTES clause is an ElevateDB extension.
FROM PUBLISHED UPDATESThe FROM PUBLISHED UPDATES clause is an ElevateDB extension.
FROM UPDATESThe FROM UPDATES clause is an ElevateDB extension.
Image