|
Define a persistent base table, a global temporary table, or a local temporary table.
Syntax
<table definition> ::=
<collation clause> ::=
<table contents source> ::=
<table element> ::=
<like clause> ::= LIKE [ schema-name. ] table-name
<as subquery clause> ::=
|
|
|
[ ( column-name [ { , column-name }... ] ) ] AS <subquery>
WITH { NO DATA | DATA }
|
<column definition> ::=
<default clause> ::= DEFAULT <default option>
<default option> ::=
|
|
|
|
|
|
|
|
|
|
<literal>
NULL
EMPTY
NEWGUID
CURRENT_DATE
{ CURRENT_TIME | LOCALTIME }
{ CURRENT_TIMESTAMP | LOCALTIMESTAMP }
{ USER | CURRENT_USER | SESSION_USER }
|
<column constraint definition> ::= [ <constraint name definition> ] <column constraint>
<constraint name definition> ::= CONSTRAINT constraint-name
<column constraint> ::=
<table constraint definition> ::= [ <constraint name definition> ] <table constraint>
<table constraint> ::=
<unique constraint definition> ::= { UNIQUE | PRIMARY KEY } ( column-name [ { , column-name }... ] )
<referential constraint definition> ::=
<references specification> ::=
<referential triggered action> ::=
<update rule> ::= ON UPDATE <referential action>
<delete rule> ::= ON DELETE <referential action>
<referential action> ::=
|
|
|
|
|
|
CASCADE
SET NULL
SET DEFAULT
RESTRICT
|
<check constraint definition> ::= CHECK ( <search condition> )
Usage
The CREATE TABLE statement creates a new table in the database with metadata according to the table definition.
§
|
The schema and table names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The table is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.
|
Table attributes
The following table descriptor attributes are NexusDB extensions that can be used to tailor the table definition:
§
|
DESCRIPTION is a free text attribute used to store a comment in the table descriptor.
|
§
|
BLOCKSIZE sets the physical page size to one of the valid values 4, 8, 16, 32 or 64 kilobytes. The default value is 4. The page size is automatically adjusted to fit the total row size.
|
§
|
INITIALSIZE sets the initial number of allocated pages. The default value is 4.
|
§
|
GROWSIZE sets the number of new pages allocated when more space is needed. The default value is 1.
|
§
|
STORAGE ENGINE specifies the name of the storage engine used for storing data in the table. The names of the built-in storage engines are 'Static', which stores data in fixed length, and 'Variable', which stores data in variable length. The default storage engine is 'Static'.
|
§
|
ENCRYPTION ENGINE specifies the name of the encryption engine used to encrypt the table. The name of the built-in encryption engine is 'nx1xDefault'. NexusDB tables are not encrypted if an encryption engine is not explicitly specified.
|
§
|
If ENCRYPT WITH is specified, then the encryption engine will use the password as part of the encryption algorithm. A password-encrypted table cannot be accessed unless the password has been added to the list of passwords available in the current session, see the SET PASSWORDS statement.
|
§
|
DEFAULT CHARACTER SET or CODEPAGE can be used interchangeably to override the implicit codepage associated with the default collation for the table.
|
§
|
DEFAULT COLLATION or LOCALE can be used interchangeably to set a default collation for the table. If the clause is omitted, then the default collation is byte order for character string columns and a locale-neutral collation for national character string columns.
|
Note: Table attributes must be specified in the order they appear in the syntax specification.
Table contents
The following elements can be used to define the table contents:
§
|
Table constraint definition
|
Column definition
A column definition consists of the following column descriptor attributes:
§
|
Default clause (optional)
|
§
|
Column constraints (optional)
|
§
|
The column name shall conform to the format rules for identifiers in NexusDB SQL.
|
§
|
A character set can be specified for character string columns to override the default codepage associated with the implicit or explicit collation for the column.
|
§
|
The DEFAULT clause is used to assign a default value to the column when inserting new rows, with an implicit null value if the clause is omitted.
|
§
|
DESCRIPTION is a free text attribute that can be used to store comments in the column descriptor.
|
Column and table constraints
§
|
The NOT NULL constraint can only be defined as a column constraint, and prohibits the null value to be assigned to the column in any row of the table.
|
§
|
The UNIQUE constraint can be defined either as a column constraint, in case of an one-column key, or as a table constraint with one ore more columns specified in the column list. Rows that have the null value in any of the columns contained in the UNIQUE constraint are not included in the uniqueness check implied by the constraint, hence UNIQUE alone, without also specifying NOT NULL, permits multiple null values in a column.
|
§
|
The PRIMARY KEY constraint is equal to the UNIQUE constraint, except that null values are not allowed in any column of the primary key. NOT NULL is implicit for primary key columns if not explicitly specified. Only one PRIMARY KEY constraint can be defined on a table.
|
§
|
A FOREIGN KEY is a single column or a set of columns within a table that is referencing equivalent columns in another table through its values, a relationship called Referential Integrity. The purpose of the FOREIGN KEY constraint is to ensure that rows in the referencing table always have corresponding rows in the referenced table. The FOREIGN KEY constraint can be defined either as a column constraint using the REFERENCES clause only, in case of an one-column key, or as a table constraint with one ore more referencing columns specified in the column list.
|
§
|
If the REFERENCES clause specifies a column list, then there shall be a one-to-one correspondence between the set of column names specified in the REFERENCES clause and the set of column names contained in a PRIMARY KEY or UNIQUE constraint of the referenced table. If the column list is not specified, then the referenced table shall have a PRIMARY KEY constraint containing a set of columns that matches the set of referencing columns.
|
§
|
The data types of referencing and referenced columns shall be comparable.
|
§
|
The REFERENCES clause may optionally specify an update rule, a delete rule, or both rules, each defining one of the following referential actions:
|
§
|
RESTRICT, the default referential action, aborts the current transaction with a foreign key violation error if the referential constraint is violated during the execution of an UPDATE or DELETE statement on the referenced table.
|
§
|
If CASCADE is specified, then data changes in the referenced table are replicated to the corresponding rows in the referencing table.
|
§
|
If SET NULL is specified, then referencing columns are set to null when a corresponding row in the referenced table is deleted or the referenced column is changed.
|
§
|
If SET DEFAULT is specified, then referencing columns are set to their default value when a corresponding row in the referenced table is deleted or the referenced column is changed.
|
§
|
The CHECK constraint is a general purpose constraint that can be defined either as a column constraint or a table constraint. The search condition can be any valid predicate. Columns of the table defining the constraint are referenced by the column names, while columns of other tables can be accessed by using a subquery. The CHECK constraint is satisfied if the search condition is not False for any row in the table, as opposed to having to be True for every row in the table.
|
§
|
A user-defined constraint name can be specified for both column and table constraints, except the NOT NULL constraint. The constraint name shall conform to the format rules for identifiers in NexusDB SQL. If the constraint name is not specified, then the constraint being defined is given a system-generated name. The main advantage of using named constraints, is that this feature makes constraint management a lot easier.
|
§
|
The implicit constraint mode is INITIALLY IMMEDIATE NOT DEFERRABLE.
|
Note: The ALTER TABLE statement is used for constraint management.
LIKE clause
§
|
The LIKE clause works like a shorthand for manually defining new columns based on the columns of an existing table, and can be mixed with other column definitions and table constraints.
|
§
|
The LIKE clause defines a new column for each column in the source table and copies the following column descriptor attributes to the new table:
|
§
|
Data type (including explicit character set and collation for character string columns)
|
§
|
Column constraints other than the NOT NULL constraint are not copied to the new table.
|
AS subquery clause
§
|
The AS subquery clause creates a new table based on one or more existing tables using a subquery.
|
§
|
The optional column-name list is used to rename the columns specified in the select list of the subquery. If the column-name list is specified, then the number of columns shall be the same as the degree of the subquery.
|
§
|
For each column in the subquery result set, the following column descriptor attributes are copied to the new table:
|
§
|
Data type (including explicit character set and collation for character string columns)
|
§
|
No table constraints or indexes are copied to the new table.
|
§
|
If WITH DATA is specified, then the new table is populated with the rows from the subquery result set.
|
Temporary tables
By supporting temporary tables, NexusDB provides an efficient concept for storing sets of data temporarily, without worrying about conflicts with persistent data in the database. Temporary tables are useful in many situations, for example to create fast lookup tables locally on the client, to combine data from multiple tables, and to store intermediate result sets that can be accessed and further processed by other statements.
NexusDB creates temporary tables in memory. Once created, temporary tables can be manipulated and queried just like ordinary tables, except that the visibility is limited to the scope of the temporary table type. NexusDB SQL supports three types of temporary tables with different scopes and duration:
§
|
A context-local temporary table is defined by prefixing the table name with a single pound symbol (#table-name). Context-local temporary tables are visible only in the execution block where they are created, and are automatically dropped when the execution block terminates. Context-local temporary tables cannot be restructured if they contain data.
|
§
|
A session-local temporary table is defined by prefixing the table name with two pound symbols (##table-name). Session-local temporary tables are visible to all processes that are executed in the context of the session and database object that created them, and are automatically dropped when the session terminates.
|
§
|
A global temporary table is defined by prefixing the table name with three pound symbols (###table-name). Global temporary tables are visible to all sessions accessing the server, and stay in memory on the server until explicitly dropped or the server shuts down.
|
§
|
The V1 syntax of defining a global temporary table by enclosing the table name in angle brackets (<table-name>) is still supported, but has been deprecated and may be removed in a future version of NexusDB SQL.
|
§
|
NexusDB SQL requires that the names of temporary tables are prefixed with pound symbols. The optional GLOBAL | LOCAL TEMPORARY TABLE syntax can be used when defining temporary tables, but is only supported for clarity and syntax compatibility with SQL:2003.
|
Tip: The AS subquery clause and the SELECT INTO statement are both convenient ways of creating temporary tables based on existing tables.
Examples
1)
|
The following example shows a table definition with column constraints:
|
CREATE TABLE customers
/*
We're defining a default collation for this table.
The default collation is used in sort and comparison operations on character string columns that don't explicitly specify a collation on the column level.
*/
DEFAULT LOCALE 1033
(
customerID AUTOINC NOT NULL PRIMARY KEY,
companyName VARCHAR(40),
address1 VARCHAR(30),
address2 VARCHAR(30),
city VARCHAR(20),
postalCode VARCHAR(10),
country VARCHAR(20)
)
2)
|
The following example shows a table definition with default column values and column constraints:
|
CREATE TABLE orders
DESCRIPTION 'Master sales orders table'
(
orderID AUTOINC NOT NULL PRIMARY KEY,
customerID INTEGER NOT NULL REFERENCES customers ( customerID ),
orderDate DATE DEFAULT CURRENT_DATE,
shippingDate DATE,
freight DECIMAL(0,2) DEFAULT 0,
orderTotal DECIMAL(0,2) DEFAULT 0,
amountPaid DECIMAL(0,2) DEFAULT 0
)
3)
|
The following example shows a table definition with named column and table constraints:
|
CREATE TABLE order_details
(
orderID INTEGER NOT NULL CONSTRAINT uc_orderID UNIQUE,
productID AUTOINC NOT NULL,
unitPrice DECIMAL(0,4) DEFAULT 0,
quantity SMALLINT DEFAULT 0,
discount FLOAT DEFAULT 0,
CONSTRAINT pk_orderID_productID PRIMARY KEY ( orderID, productID ),
CONSTRAINT fk_orders_orderID FOREIGN KEY ( orderID ) REFERENCES orders ( orderID )
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ck_discount CHECK ( discount >= 0 )
)
4)
|
The following example illustrates how the LIKE clause is used in a table definition:
|
/*
The LOCAL TEMPORARY keywords have been included for syntax clarity.
What's actually defining the table as a session-local temporary table in NexusDB are the two pound signs prefixing the table name.
*/
CREATE LOCAL TEMPORARY TABLE ##students
(
LIKE students,
dob DATE,
notes CLOB LOCALE 1033, -- A collation is explicitly specified for this column
primary key ( studentID )
)
5)
|
The following example illustrates how the AS subquery clause is used in a table definition:
|
/*
The three pound signs prefixing the table name defines the table as a global temporary table.
The GLOBAL TEMPORARY keywords have been omitted, but could have been specified for syntax clarity.
CREATE TABLE ###students_list
( studentID, name, city, gender ) AS
( SELECT studentID, studentName, city, gender FROM students )
WITH DATA
Conformance
SQL:2003 standard
NexusDB extensions
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
|
Core SQL
Feature F692 "Enhanced collation support"
Feature T171 "LIKE clause in table definition"
Feature T172 "AS subquery clause in table definition"
Feature F531 "Temporary tables"
Rows of temporary tables are implicitly preserved
Feature F491 "Constraint management"
Feature T591 "UNIQUE constraints of possibly null columns"
Feature F191 "Referential delete actions"
Feature F701 "Referential update actions"
Feature T191 "Referential action RESTRICT"
Feature T201 "Comparable data types for referential constraints"
Feature F671, "Subqueries in CHECK constraints"
Feature F672 "Retrospective check constraints"
DESCRIPTION
BLOCKSIZE | BLOCK
INITIALSIZE | INITIAL
GROWSIZE | GROW
STORAGE [ ENGINE ]
ENCRYPTION [ ENGINE ]
ENCRYPT WITH
DEFAULT CHARACTER SET
DEFAULT COLLATION
NEWGUID
#table-name defines a context-local temporary table
##table-name defines a session-local temporary table
###table-name defines a global temporary table
|
|
