Skip to main content

Table elements

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

Identity columns take their values from a sequence generator. The MySQL (auto_increment) and PostgreSQL (serial data type) syntax are also supported. The SERIAL type is mapped to either an INTEGER or BIGINT (when using BIGSERIAL).

Column and Table constraints are both supported. Besides the simple not null check also unique, primary, and foreign keys are supported. We currently check constraints directly on insert, update and delete, so immediate. This limitation stems from the missing triggers. The null matching on foreign keys is limited to the simple match type (null values satisfy the constraint). The full and partial match types are not supported. The referential action is currently limited to restrict, i.e. an update fails if other columns have references to it. The CHECK constraints are currently not enforced. Also they are not recorded in a data dictionary table.

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

A table constraint definition is not tied to a particular column, but rather to a column group. A column constraint is a notational convenience when the constraint only affects one column.

column_option:
    DEFAULT default_value
   | [ constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

 

column_constraint_type:
    NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK '(' column_logical_expression ')'
  | 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 }

A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A ful match requires all column values to be not null unless all are null.The simple match is more relaxed; any of the keys may be null. The partial match is consider noise for the time being.

The serial columns are a slight extension over the serial types defined earlier. In particular, we can designate RESTART option when the sequence is exhausted. This may involve a subquery over the database.

serial_parameters:
     [ START WITH nonzero-bigint ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero-bigint ]
   | [INCREMENT BY nonzero-bigint ]
   | [MINVALUE nonzero-bigint | NOMINVALUE]
   | [MAXVALUE nonzero-bigint | NOMAXVALUE ]
   | [CACHE nonzero-bigint ]
   | [CYCLE | NOCYCLE]