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 AS IDENTITY [ '(' serial_parameters ')' ]
| AUTO_INCREMENT
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 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.
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 NOT NULL constraint, UNIQUE, PRIMARY KEY and FOREIGN KEY constraints are supported. A table can have at most one primary key definition. We currently check constraints directly on insert, update and delete, so immediate. Column and Table constraints are both supported.
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.
column_constraint_type:
NOT NULL
| NULL
| UNIQUE
| PRIMARY KEY
| 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 }
table_constraint_type:
UNIQUE '(' column_name [ ',' ... ] ')'
| PRIMARY KEY '(' column_name [ ',' ... ] ')'
| FOREIGN KEY '(' column_name [ ',' ... ] ')' REFERENCES table_name [ '(' column_name [ ',' ... ] ')' ]
[ match_options ] [ ref_actions ]
Note: The column CHECK constraint definitions are no longer accepted by the parser as of Nov2019 (11.35.3) release. They used to be accepted (for ease of migration) but were not enforced nor recorded in a data dictionary table.
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.
A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A full 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 considered 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]