Column Specification
The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.
Note: For Vector tables, defaults, nullability, and identity columns work only for INSERT...VALUE, not INSERT SELECTs.
The column_specification has the following format:
column_name datatype
[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT]
[WITH NULL | NOT NULL]
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
[[CONSTRAINT constraint_name] column_constraint
{ [CONSTRAINT constraint_name] column_constraint}]
[ENCRYPT [SALT | NOSALT]]
column_name
datatype
Assigns a valid data type to the column. If CREATE TABLE...AS SELECT is specified, the new table takes its column names and formats from the results of the SELECT clause of the subselect specified in the AS clause (unless different column names are specified).
Note: For char and varchar columns, the column specification is in number of bytes (not number of characters).
DEFAULT clause
Specifies whether the column is mandatory.
WITH NULL | NOT NULL
Specifies whether the column accept nulls:
WITH NULL
(Default) Indicates that the column accepts nulls. If no value is supplied by the user, null is inserted.
NOT NULL
Indicates that the column does not accept nulls.
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates the column is an
IDENTITY Columns. The column must be defined as integer or bigint.
[CONSTRAINT constraint_name] column_constraint
Specifies checks to be performed on the contents of the column to ensure appropriate data values.
ENCRYPT [SALT | NOSALT]
Specifies that the column values be encrypted. ENCRYPT must be used with the WITH ENCRYPTION option, as described in
WITH Clause.
All data types can be encrypted. An encrypted column cannot be part of a table key.
By default, encrypted columns use salt, which is 16 bytes of random bits added to the encrypted column to further obfuscate the value. For reasons to specify NOSALT, see Understanding SALT in the Vector Security Guide.