» Documentation » SQL Reference

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 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 DELETE {CASCADE | NOACTION}]} 

type_name syntax

AUTOBIGINT | BIGINT | INT | TINYINT | SMALLINT | DOUBLE | FLOAT | DECIMAL(x,y) | BIT
MONEY | SMALLMONEY | DATETIME | CHAR | VARCHAR |  NCHAR | NVARCHAR | TEXT | NEXT |GUID | BLOB 

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 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 partitioning columns, read Distributed schema 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 {DELETE} CASCADE option will mean that if the matching row in the foreign table is 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.

NOTE: table without Primary Index is not allowed since version 4.0

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 AUTOBIGINT NOT NULL PRIMARY KEY PARTITION,
  author_name VARCHAR
);
CREATE TABLE book 
(
  book_id AUTOBIGINT NOT NULL PRIMARY KEY,
  author_id BIGINT PARTITION REFERENCES author(author_id),
  book_title VARCHAR
);