Column Specification--Define Column Characteristics
The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.
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}]
[COLLATE collation_name]
[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, as described in
Default Clause (see
Default Clause).
NULL clause
Specifies whether the column accept nulls, as described in
Null Clause (see
Null Clause).
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates the column is an
Identity Column (see
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, as described in
Constraints (see
Constraints).
COLLATE collation_name
Specifies a column-level collation sequence, as one of the following.
Note: A default collation sequence can be specified for the database during database creation. The column-level collation overrides the default collation set for the database. For more information, see createdb command in the Command Reference Guide.
UCS_BASIC
Specifies collation for columns containing Unicode data (nchar and nvarchar data types) or all character data types when using UTF8. Use this when performance and efficiency is more important than the order that the data is collated in. This is the default collation for Vector.
UNICODE
Specifies collation for columns containing Unicode data. This is the default collation for Unicode columns (nchar, nvarchar and long nvarchar) and for char, varchar, and long varchar columns if the instance is installed with the UTF8 character set.
UNICODE_CASE_INSENSITIVE
Specifies case insensitive collation for columns containing Unicode data.
UNICODE_FRENCH
Specifies French collation for columns containing Unicode data.
SQL_CHARACTER
Specifies the collation for columns containing char, C, varchar, and text data. This is the default collation for columns with non-Unicode data.
ENCRYPT [SALT|NOSALT]
Specifies that the column values be encrypted. ENCRYPT must be used with the WITH ENCRYPTION option, as described in
With_Clause for Create Table (see
With_Clause for Create Table).
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. The NOSALT specification is required for encrypted columns that will be indexed.
For more information on encrypting columns, see the Security Guide.