Table Elements

table_element:
     column_name   data_type   [ column_option ... ]
   | column_name { SERIAL | BIGSERIAL }
   | column_name   WITH OPTIONS '(' column_option [ ',' ... ] ')'
   | LIKE table_name
   | [ CONSTRAINT constraint_name ] table_constraint_type

column_option:
     DEFAULT default_value_expr
   | [ CONSTRAINT constraint_name ] column_constraint_type
   | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

column_constraint_type:
     NOT NULL
   | NULL
   | UNIQUE
   | PRIMARY KEY
   | REFERENCES table_name [ '(' column_name [ ',' ... ')' ] ]   [ match_options ]   [ ref_actions ]

table_constraint_type:
     UNIQUE '(' column_name [ ',' ... ] ')'
   | PRIMARY KEY '(' column_name [ ',' ... ] ')'
   | FOREIGN KEY '(' column_name [ ',' ... ] ')' REFERENCES table_name [ '(' column_name [ ',' ... ] ')' ]
     [ match_options ]   [ ref_actions ]

match_options:
     MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     { ON UPDATE | ON DELETE }  { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }

See Data types for a list of all available data types and syntax.

The SERIAL type is an identity column which is mapped to an INTEGER NOT NULL PRIMARY KEY. Similarly the BIGSERIAL type is an identity column which is mapped to a BIGINT NOT NULL PRIMARY KEY. Identity columns take their values from a sequence generator.

You can also use LIKE qname as part of the column definition to copy the column definitions of qname excluding their constraints. For instance:

CREATE TABLE webshop.products_new (LIKE webshop.products, descr VARCHAR(9999), pict BLOB);

The constraints NOT NULL, UNIQUE, PRIMARY KEY and FOREIGN KEY are supported. Both Column and Table constraint syntax variants are supported.

A table can have at most one primary key definition. When defined the primary key columns will all get a NOT NULL constraint also.

A table can have multiple unique constraints, including on nullable columns.

A table can have multiple foreign key constraints, but each must reference an existing primary key or unique constraint. Some limitations exist for foreign key constraints. The null matching on foreign keys is limited to the SIMPLE match type (any of the keys may be null and null values satisfy the constraint). The FULL and PARTIAL match types are not supported. A full match requires all column values to be not null unless all are null.
The referential action is currently limited to RESTRICT, i.e. an update fails if other columns have references to it.

All constraints are always checked directly on insert, update, delete or truncate statements, so IMMEDIATE behavior.

Associated system tables: sys.columns, sys.keys, sys.fkeys, sys.objects, sys.sequences.