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 [ '(' sequence_parameters ')' ]
   | AUTO_INCREMENT

column_constraint_type:
     NOT NULL
   | NULL
   | CHECK '(' search_condition ')'
   | UNIQUE
   | UNIQUE NULLS DISTINCT
   | UNIQUE NULLS NOT DISTINCT
   | PRIMARY KEY
   | REFERENCES table_name [ '(' column_name [ ',' ... ')' ] ]
         [ match_option ]  [ ref_actions ]

table_constraint_type:
     CHECK '(' search_condition ')'
   | UNIQUE '(' column_name [ ',' ... ] ')'
   | UNIQUE NULLS DISTINCT '(' column_name [ ',' ... ] ')'
   | UNIQUE NULLS NOT DISTINCT '(' column_name [ ',' ... ] ')'
   | PRIMARY KEY '(' column_name [ ',' ... ] ')'
   | FOREIGN KEY '(' column_name [ ',' ... ] ')'
       REFERENCES table_name [ '(' column_name [ ',' ... ] ')' ]
         [ match_option ]  [ ref_actions ]

match_option:
     MATCH [ FULL | PARTIAL | SIMPLE ]

ref_actions:
     on_update_action
   | on_delete_action
   | on_update_action  on_delete_action
   | on_delete_action  on_update_action

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

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

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

The SERIAL and BIGSERIAL data types and GENERATED AS IDENTITY and AUTO_INCREMENT column options are used to specify an Identity column. These take their values from an integer number sequence generator, starting from 1.
SERIAL will use an int as column data type, BIGSERIAL will use a bigint as column data type.
Note that a SERIAL or BIGSERIAL column will automatically also generate a primary key constraint on the column.
See seq_parameters definition for the allowed sequence_parameters syntax.

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

Constraints

All standard SQL constraints: NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE and UNIQUE NULLS NOT DISTINCT are supported. Both Column and Table constraint syntax variants are supported, see above.

If no constraint_name is specified a constraint_name will be composed internally from the table name, column name(s) and constraint type.

A table can have at most one primary key constraint. When defined the primary key column(s) will automatically also all get a NOT NULL constraint. If you do not want this, use a unique constraint instead.

A table can have multiple check constraints.

A table can have multiple unique constraints, including on nullable columns. You can specify how NULLs in multiple rows are compared. When specifying UNIQUE or UNIQUE NULLS DISTINCT, every NULL in the column will be treated as distinct from other rows and thus are allowed. When specifying UNIQUE NULLS NOT DISTINCT, NULLs in the column will be treated as equal and thus NULL in multiple rows are disallowed.

A table can have multiple foreign key constraints, each must reference an existing primary key or unique constraint. When no ON UPDATE action is specified ON UPDATE RESTRICT is used as default. When no ON DELETE action is specified ON DELETE RESTRICT is used as default. When no match option is specified MATCH SIMPLE is used as default.

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 yet implemented. 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 information_schema views: information_schema.columns, information_schema.check_constraints, information_schema.table_constraints, information_schema.referential_constraints.

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