CREATE TABLE

SQL Guide : solidDB® SQL statements : CREATE TABLE

CREATE TABLE

CREATE [ { [GLOBAL] TEMPORARY | TRANSIENT } ]
TABLE base_table_name
(column_element [, column_element] ...) [STORE {MEMORY | DISK}]
[LIKE table_name]

base_table_name ::= base_table_identifier | schema_name.base_table_identifier |
catalog_name.schema_name.base_table_identifier

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 |
   REFERENCES ref_table_name [(referenced_columns)] |
   CHECK (check_condition)

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 table_name [(referenced_columns)]

[referential_triggered_action] } referential_triggered_action:: =
ON {UPDATE | DELETE} {CASCADE | SET NULL | SET DEFAULT |
RESTRICT | NO ACTION}

Usage

Tables are created through the CREATE TABLE statement. The CREATE TABLE statement requires a list of the columns created, the data types, and, if applicable, sizes of values within each column, in addition to other options, such as creating primary keys.

If the table name or column names starts with a digit, it must be given in double quotation marks.

STORE (table type)

The STORE clause indicates whether the table should be stored in memory or on disk. The CREATE TABLE statement takes precedence over the storage location specified by the General.DefaultStoreIsMemory parameter.

In-memory tables can be persistent (normal) tables, temporary tables, or transient tables.

All temporary tables and transient tables must be in-memory tables. You do not need to specify the STORE MEMORY clause; temporary tables and transient tables will automatically be created as in-memory tables if you omit the STORE clause. For temporary tables and transient tables, the General.DefaultStoreIsMemory parameter is ignored. You will get an error if you try to explicitly create temporary tables or transient tables as disk-based tables.

The keyword "GLOBAL" is 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.

Row size of disk-based and in-memory tables

In a disk-based table, the maximum size of a row (excluding BLOBs) is approximately 1/3 of the page size. In an in-memory table, the maximum size of a row (including BLOBs) is approximately the page size. (There is a small amount of overhead used in both disk-based and in-memory pages, so all of the page is not available for user data.) The page sized (block size) is defined with the IndexFile.BlockSize parameter (default is 16 KB).

The server 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.

Each row is limited to 1000 columns.

Referential integrity

You should always define a primary key when you create a table. If you do not define a primary key, solidDB^® will create one automatically. This will lead to unexpected data order on the disk and may cause performance degradation. An appropriate primary key speeds up queries using the primary key.

Constraint definitions are available for both the column and table level. For the column level, constraints defined with NOT NULL specify that a non-null value is required for a column insertion. UNIQUE specifies that no two rows are allowed to have the same value. PRIMARY KEY ensures that the column(s), which is (are) a primary key, does not permit two rows to have the same value and does not permit any NULL values; PRIMARY KEY is thus equivalent to the combination of UNIQUE and NOT NULL. The REFERENCES clause with FOREIGN KEY specifies a table name and a list of columns for a referential integrity constraint. This means that when data is inserted or updated in this table, the data must match the values in the referenced tables and columns.

The CHECK keyword restricts the values that can be inserted into a column (for example, restricting the values with a specific integer range). When defined, the check constraint performs a validation check for 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));

The check_condition is a boolean expression that specifies the check constraints for the column. Check constraints are defined with the predicates >, <, =, <>, <=, >= and the keywords BETWEEN, IN, LIKE (which may contain wildcard characters), and IS [NOT] NULL. The expression (similar to the syntax of a WHERE clause) can be qualified with keywords AND and OR. For example:

...CHECK (col1 = ’Y’ OR col1 = ’N’)... ...CHECK (last_name IS NOT NULL)...

UNIQUE and PRIMARY KEY constraints can be defined at the column level or the table level. They also automatically create a unique index on the specified columns.

FOREIGN KEY is used to specify that the column(s) listed are foreign keys in this table.

REFERENCES specifies the table and those column(s) that are references of the foreign key(s). Although column-level constraints can use a REFERENCES clause, only table-level constraints can use the FOREIGN KEY ... REFERENCES clause.

To use the REFERENCES constraint with FOREIGN keys, a foreign key must always include enough columns in its definition 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:

▪The check_condition cannot contain subqueries, aggregate functions, host variables, or parameters.

▪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 may have only one primary key constraint, but may 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, a column cannot be dropped if it is part of a unique or primary key. You may want to use the CREATE INDEX statement to create an index instead – this way the index will have a name and you can drop it. 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.

▪The referential integrity rules for persistent, transient, and temporary table types are different.

– A temporary table may reference another temporary table, but may not reference any other type of table (transient or persistent). No other type of table may 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.

Creating tables like existing tables

Instead of providing the table definition in the CREATE TABLE statement, you can create a table with the same definition as in an existing table by using the CREATE new_table_name LIKE existing_table_name construction.

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 constraint

▪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 to the current catalog and schema.

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);

Related information

Data types