solidDB Help : solidDB reference : SQL: Statements : CREATE TABLE
  
CREATE TABLE
Note Check the solidDB Release Notes for any limitations that are associated with using this statement with a grid in the current release.
CREATE [{[GLOBAL] TEMPORARY | TRANSIENT } SHARED ] TABLE table‑element
   (column‑element [, column‑element …])
   [STORE {MEMORY | DISK}]
   [LIKE table‑element] COMPRESSED
   [grid-element]
where:
table‑element::= [[catalog‑name.]schema‑name.]table‑name
column‑element::= column‑definition | table‑constraint‑definition
column‑definition::= column‑identifier
   data‑type [DEFAULT {literal | NULL}] [NOT NULL]
      [column‑constraint‑definition [column‑constraint‑definition] …]
column‑constraint‑definition::= [CONSTRAINT constraint‑name]
   {UNIQUE |
   PRIMARY KEY |
   CHECK (check‑condition) |
   REFERENCES ref‑table‑name [(referenced‑columns)]}
table‑constraint‑definition::= [CONSTRAINT constraint‑name]
   {UNIQUE (column‑identifier [, column‑identifier] …) |
   PRIMARY KEY (column‑identifier [, column‑identifier] …) |
   CHECK (check‑condition) |
   FOREIGN KEY (column‑identifier [, column‑identifier] …)
      REFERENCES ref-table‑name [(referenced‑columns)] [referential‑triggered‑action]}
referential‑triggered‑action::=
   ON {UPDATE | DELETE} {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
grid-element::= [PARTITION BY (partitioning‑key)]
   [REPLICATION FACTOR factor]
check‑condition ::= (see check-condition)
data‑type::= (see data-type)
Access requirements
Database user
Usage
Use the CREATE TABLE statement to create a table and specify the required columns, column data types, and, if applicable, sizes of values within each column.
Note If the table name or any column name starts with a digit, the name must be surrounded by double quotation marks.
Parameters, clauses, keywords, and variables
▪ GLOBAL: Included to comply with the SQL:1999 standard for temporary tables. In solidDB, all temporary tables are global, whether or not the GLOBAL keyword is used.
▪ STORE: Indicates whether the table should be stored in memory (M-table) or on disk (D-table), see Data storage in solidDB. The CREATE TABLE statement takes precedence over the storage location that is specified by the General.DefaultStoreIsMemory parameter, see General section.
In disk-based tables (D-table), the maximum size of a row is 2GB. However, if the row size exceeds approximately 1/3 of the page size, the column is stored in BLOB storage.
solidDB does not use simple rules to determine BLOB storage, but as a general rule each BLOB occupies 256 bytes from the page where the row resides, and the rest of the BLOB goes to separate BLOB pages. If the BLOB is shorter than 256 bytes, then it is stored entirely in the main disk page, not BLOB pages.
In in-memory based tables (M-table), the maximum size of a row is set by the solid.ini parameter MME.LongRowMaxLimit, see LongRowMaxLimit.
For more information on row sizes in solidDB, see Tables with long rows and BLOBs.
Each row is limited to 1000 columns.
For more information about table sizes, see Calculating storage requirements.
Note If you create temporary or transient tables, you do not need to specify the STORE MEMORY clause; temporary tables and transient tables are automatically created as in-memory tables if you omit the STORE clause. An error is generated if you try to explicitly create temporary tables or transient tables as disk-based tables.
▪ column-constraint-definition and table-constraint-definition:
– NOT NULL: Specifies that a non-null value is required.
– UNIQUE: Specifies the column (or combination of columns) that must have a unique value (or combination of values) in the table, and automatically creates an index on the column or columns.
– PRIMARY KEY: Specifies the column (or combination of columns) that must have a unique and non-null value (or combination of values) in the table, and automatically creates an index on the column or columns. For more information, see Primary key indexes.
Note If you do not define a primary key, solidDB creates one automatically. which leads to unexpected data order on the disk and can cause performance degradation.
– CHECK: Restricts the values that can be inserted into a column (for example, only allowing values in a specific integer range). When defined, a validation check is performed on any data that is inserted or updated in that column. If the data violates the constraint, then the modification is prohibited. For example:
CREATE TABLE table1 (salary DECIMAL CHECK (salary >= 0.0))
– REFERENCES or FOREIGN KEY ... REFERENCES: Specifies a table name and a list of columns for a referential integrity constraint. This means that when data is inserted or updated in the specified column (or columns) of the table that you are creating, the data must match values in the referenced table columns.
If you use the FOREIGN KEY ... REFERENCES clause, you must specify enough columns to uniquely identify a row in the referenced table. A foreign key must contain the same number and type (data type) of columns as the primary key in the referenced table as well as be in the same order; however, a foreign key can have different column names and default values than the primary key.
The following rules apply on the constraints:
▪ Column check constraints can reference only the columns on which they are defined.
▪ Table check constraints can reference any columns in the table, that is if all columns in the table have been defined earlier in the statement.
▪ A table can have only one primary key constraint, but can have multiple unique constraints.
▪ The UNIQUE and PRIMARY KEY constraints in the CREATE TABLE statement can be used to create indexes. In the ALTER TABLE statement (see ALTER TABLE), a column cannot be dropped if it is part of a unique or primary key. You might want to use the CREATE INDEX statement to create an index instead – this way the index has a name and you can drop it if required. The CREATE INDEX statement also offers some additional features, such as the ability to create non-unique indexes and to specify if the indexes are sorted in ascending or descending order, see CREATE INDEX.
▪ The referential integrity rules for persistent, transient, and temporary table types are different.
– A temporary table can reference another temporary table, but cannot reference any other type of table (transient or persistent). No other type of table can reference a temporary table.
– Transient tables can reference other transient tables and persistent tables. They cannot reference temporary tables. Neither temporary tables nor persistent tables can reference a transient table.
▪ referential‑triggered‑action: Specifies what happens to rows in this table when rows are updated or deleted in the referenced table such that the referential integrity constraint is violated:
– NO ACTION: (default) Restricts or rolls back the operation that violates the referential integrity constraint. The referential integrity check takes place at the end of the transaction so tables can temporarily violate a referential constraint as long the violation is resolved before the transaction is committed.
– CASCADE: Cascades the operations on the referenced table (parent table) down to the referencing tables (child tables). This includes deleting all the referencing rows (a cascading delete) or updating all the referencing foreign key values (a cascading update).
No cycles (table A references table B which references table A) are allowed in CASCADE referential actions. An effort to create a cycle composed of foreign keys that have cascading actions results in an error.
For any two tables, at most one CASCADE UPDATE path between them can be defined. The restriction does not apply to CASCADE DELETE.
– SET DEFAULT: Sets the referencing columns to a predefined default value.
– SET NULL: Sets the referencing columns to null.
– RESTRICT: Restricts all operations that violate the constraint. Unlike NO ACTION, the referential integrity checks take place during the transaction, so tables cannot even temporarily violate a referential constraint.
 
▪ LIKE: Creates a table with the same definition as in an existing table. For example:
CREATE TABLE MY_TABLE1(A INTEGER NOT NULL PRIMARY KEY, B INT) STORE MEMORY;
CREATE TABLE MY_TABLE2 LIKE MY_TABLE1;
The following information is copied to the new table from the existing table:
– columns,
– column data types,
– default values,
– NOT NULL constraints,
– primary key,
– table type (in-memory or disk-based table).
The following information is not copied:
– data,
– unique and secondary keys,
– foreign keys,
– check constraints.
To create tables like existing tables, you must have access rights to the existing table and rights to create tables in the current catalog and schema.
▪ grid-element: (solidDB grid only) Specifies the partitioning key and replication factor for the table.
A non-partitioned table (default) contains all the data in the table. If you use a grid, you can partition a table into multiple partitions that are distributed between the grid nodes, see Table partitioning.
– PARTITION BY: Specifies the partitioning key (the column that determines which rows are stored in the same partition).
– REPLICATION FACTOR: Specifies the replication factor (the number of grid nodes that must host a replica copy of the partition).
Examples
CREATE TABLE DEPT (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, PRIMARY KEY(DEPTNO));
CREATE TABLE DEPT2 (DEPTNO INTEGER NOT NULL PRIMARY KEY, DNAME VARCHAR);
CREATE TABLE DEPT3 (DEPTNO INTEGER NOT NULL UNIQUE, DNAME VARCHAR);
CREATE TABLE DEPT4 (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, UNIQUE(DEPTNO));
CREATE TABLE EMP (DEPTNO INTEGER, ENAME VARCHAR, FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO)) STORE DISK;
CREATE TABLE EMP2 (DEPTNO INTEGER, ENAME VARCHAR, CHECK (ENAME IS NOT NULL),
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)) STORE MEMORY;
CREATE GLOBAL TEMPORARY TABLE T1 (C1 INT);
CREATE TRANSIENT TABLE T2 (C1 INT);
CREATE TABLE "053TEST" ("5DEPT" INTEGER NOT NULL UNIQUE, "6DEPT" VARCHAR);
CREATE TABLE TABLE_1 (I INTEGER NOT NULL PRIMARY KEY, J INTEGER NOT NULL, C VARCHAR) PARTITION BY (I) REPLICATION FACTOR 3;
Go up to
SQL: Statements