1.1.1

| REPLICATE ]
[
SERVER GROUPS Clause on page 502
]
[
GATEWAYSENDER Clause on page 503
]
[
ASYNCEVENTLISTENER Clause on page 504
]
[
EVICTION BY Clause on page 504
]
[
EXPIRE Clause on page 505
] *
[
PERSISTENT Clause on page 506
]
Description
Tables contain columns and CONSTRAINT Clause, rules to which data must conform. Table-level constraints
specify a column or columns. Columns have a data type and can specify column constraints (column-level
constraints). The syntax of CREATE TABLE is extended to give properties to the tables that are specific to
SQLFire.
The CREATE TABLE statement has two variants depending on whether you are specifying the column definitions
and constraints (CREATE TABLE), or whether you are modeling the columns after the results of a query
expression (CREATE TABLE...AS...).
Example of CREATE TABLE
CREATE TABLE HOTELAVAILABILITY
(HOTEL_ID INT NOT NULL,
BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0,
PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
CREATE TABLE ... AS ...
With the alternate form of the CREATE TABLE statement, you specify the column names and/or the column
data types with a query. The columns in the query result are used as a model for creating the columns in the new
table.
If no column names are specified for the new table, then all the columns in the result of the query expression
are used to create same-named columns in the new table, of the corresponding data type(s). If one or more column
names are specified for the new table, the same number of columns must be present in the result of the query
expression; the data types of those columns are used for the corresponding columns of the new table.
The WITH NO DATA clause specifies that the data rows that result from evaluating the query expression are
not used; only the names and data types of the columns in the query result are used. The WITH NO DATA clause
must be specified.
Example of CREATE TABLE...AS...
Create a new table using all of the columns and data types from an existing table, T1:
CREATE TABLE T3 AS SELECT * FROM T1 WITH NO DATA;
vFabric SQLFire User's Guide492
vFabric SQLFire Reference