CREATE TABLE

Syntax

CREATE TABLE [IF NOT EXISTS] table_path
 (column_definition [, ...] [, table_constraint [, ...]]);

column_definition syntax

column_name type_name [[NOT] NULL]
 [DEFAULT {'constant' | (function_name())}]
 [COMMENT {'constant' | ('constant')}]
 [PARTITION]
 [PRIMARY KEY | UNIQUE]
 [REFERENCES foreign_table_path (foreign_column)
   [ON UPDATE {CASCADE | NOACTION}]
   [ON DELETE {CASCADE | NOACTION}]]

table_constraint syntax

CONSTRAINT constraint_name
 {PRIMARY KEY (column_name [, ...])
 | UNIQUE [INDEX] (column_name [, ...])
 | [NOT UNIQUE] INDEX (column_name [, ...])
 | FOREIGN KEY (column_name [, ...])
    REFERENCES foreign_table_path (foreign_column [, ...])
      [ON UPDATE {CASCADE | NOACTION}]
      [ON DELETE {CASCADE | NOACTION}]}

type_name syntax

UNIQUEIDENTIFIER | TINYINT | SMALLINT | INT | INTEGER | BIGINT
 | DOUBLE | FLOAT
 | DATETIME | CHAR | VARCHAR | BLOB | TEXT | GUID

Description

Creates a new table. table_path and foreign_table_path specify the locations of the database tables. They take the form database_name.table_name, or, if in the currently used database, it may be just table_name.

Column Definition

Each column must have at least a name and a type. The available types are listed on the ScimoreDB SQL data types page.

PARTITION

One column of every table can have the PARTITION option. The PARTITION column is used to determine the distribution of records in a cluster scenario. Equal, or similar values in a partitioned column are more likely to end up on the same server. For more information about partition columns, see the partition column page.

When PARTITION key is not set, a table becomes replicated table. In a cluster, replicated tables' content will be exactly the same on each node. All modification on replicated tables will be propagated to all nodes. Transactions are committed when all nodes completed any modifications to these replicated tables. Read transactions greatly benefit performance, since the data can be extracted from any node. However, write transactions will degrade with the increasing number of the nodes in the cluster. For instance, for OLAP queries, the best practice is to replicate dimension tables and partition (round-robin) the fact table.

DEFAULT {'constant' | (function_name())}

If specified and a column is not set on an INSERT statement, the default constant given is used. Default can currently be either a constant value, or the name of a built-in function that will return a value. Currently, the only practical functions for this are GETDATE and RAND.

COMMENT {'constant' | ('constant')}

An note or reminder for a programmer about the column's purpose, if desired. It serves no other functional purpose.

Each column can also have inline CONSTRAINTs.

Keys Constraints and Indices

There are four types of CONSTRAINT:

Syntax	Description
[NOT UNIQUE] INDEX	This is just an index of where rows are for faster lookup of queries, it imposes no actual constraint on the column. This is the minimum requirement for a FOREIGN KEY to reference this column.
UNIQUE [INDEX]	A unique index across all server instances.
PRIMARY KEY	One of these is per table, it is implicitly UNIQUE and use B+TREE structure to store data.
FOREIGN KEY	This constrains the set of columns such that they must match a row in the referenced columns of the referenced table in the order specified. If there is no match on INSERT or UPDATE, an error will be triggered. A foreign key requires an index of the correct configuration to be already present in the foreign table. The ON {UPDATE \| DELETE} CASCADE option will mean that if the matching row in the foreign table is updated or deleted, the corresponding row in the referencing table will also be updated or deleted.

Single column constraints can be specified inline with the column_definition. Multi-column constraints must be specified as a table constraint.

A UNIQUE key will stay unique across all server instances. However, a UNIQUE key that does not start with the PARTITION column will be less efficient on INSERT and UPDATE statements, as all server instances have to be checked for duplicate values. So, it is faster to use non-unique indices than unique indices.

Table without Primary (Clustered) Index

Heap is a table without a clustered index. Heap table will make hidden clustered key with single auto increment hidden column and B+TREE 100/0 split ratio. Heaps usually occupy half the disk and memory space, which can greatly improve performance.

Examples

Simplest Possible Example

CREATE DATABASE IF NOT EXISTS example; GO; USE example;
DROP TABLE IF EXISTS basic_table;
CREATE TABLE basic_table (c1 INT NOT NULL PRIMARY KEY PARTITION);

Simple Foreign Key Example

CREATE DATABASE IF NOT EXISTS example; GO; USE example;
DROP TABLE IF EXISTS author;
CREATE TABLE author (
  author_id INT NOT NULL PRIMARY KEY PARTITION,
  author_name VARCHAR
);
CREATE TABLE book (
  book_id INT NOT NULL PRIMARY KEY,
  author_id INT PARTITION REFERENCES author (author_id),
  book_title VARCHAR
);

Related