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]
[MASKED [AS {BASIC | NULL | 0 | ' ' }]
[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. For more information, see
DEFAULT Clause.
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.
[MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }]
Displays the column with the specified mask characteristic unless the user has the UNMASK privilege. The mask characteristics defines how to display the masked data:
BASIC – Fills the width of the column with asterisks
NULL – NULL
0 – 0
‘ ‘ – blank
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.
DEFAULT Clause
The WITH|NOT DEFAULT clause in the column specification specifies whether a column requires an entry.
This clause has the following format:
[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT
[WITH] DEFAULT default_spec
Indicates that if no value is provided (because none is required), the DBMS Server inserts the default value. The default value must be compatible with the data type of the column.
For character columns, valid default values include the constants: USER, CURRENT_USER, and SYSTEM_USER.
For Boolean columns, valid default values include FALSE or TRUE.
WITH DEFAULT
Indicates that if no value is provided, the DBMS Server inserts 0 for numeric and money columns, an empty string for character columns, the current date for ANSI date columns, and the current timestamp for timestamp columns.
NOT DEFAULT
Indicates the column is mandatory (requires an entry).
The following is an example of using the DEFAULT clause:
CREATE TABLE DEPT(dname CHAR(10),
location CHAR(10) DEFAULT 'NY',
creation DATE DEFAULT '01/01/12',
budget MONEY DEFAULT 10000);
Restrictions on the Default Value for a Column
The following considerations and restrictions apply when specifying a default value for a column:
• The data type and length of the default value must not conflict with the data type and length of the column.
• The maximum length for a default value is 1500 characters.
• For fixed-length string columns, if the column is wider than the default value, the default value is padded with blanks to the declared width of the column.
• For numeric columns that accept fractional values (floating point and decimal), the decimal point character specified for the default value must match the decimal point character in effect when the value is inserted. To specify the decimal point character, set II_DECIMAL.
• For money columns, the default value can be exact numeric (integer or decimal), approximate numeric (floating point), or a string specifying a valid money value. The decimal point and currency sign characters specified in the default value must match those in effect when the value is inserted.
• For date columns, the default value must be a string representing a valid date. If the time zone is omitted, the time zone defaults to the time zone of the user inserting the row.
Null Clause and Default Clause Combinations
The WITH|NOT NULL clause works in combination with the WITH|NOT DEFAULT clause, as follows:
WITH NULL
The column accepts nulls. If no value is provided, the DBMS Server inserts a null.
WITH NULL WITH DEFAULT
The column accepts null values. If no value is provided, the DBMS Server inserts a 0 or blank string, depending on the data type.
WITH NULL NOT DEFAULT
The column accepts null values. The user must provide a value (mandatory column).
NOT NULL WITH DEFAULT
The column does not accept nulls. If no value is provided, the DBMS Server inserts 0 for numeric and money columns, or an empty string for character and date columns.
NOT NULL NOT DEFAULT (or NOT NULL)
The column is mandatory and does not accept nulls, which is typical for primary key columns.
Masked Column Default Values
For masked columns, the default value for the BASIC, NULL, 0, and ‘ ‘ mask characteristics by data type are as follows:
Note: In the following table, N/A means that trying to mask the column with that characteristic returns an error. For character types, the number of asterisks in the mask matches the defined size of the data type.
IDENTITY Columns
Note: Sequences, including identity columns, work for Vector tables only in specific circumstances; we do not recommend the use of sequences or identity columns except in consultation with Actian Support or Actian Services.
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence.
An identity column is a way to automatically generate a unique numeric value for each row in a table. A table can have only one column that is defined with the identity attribute.
The IDENTITY clause has the following format:
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
where:
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
Indicates that the column value is determined by the corresponding sequence. The user cannot specify an explicit value for the column in an INSERT or UPDATE statement.
INSERT statements that contain ALWAYS identity columns in their column list must specify DEFAULT as the corresponding value. To override this behavior, use the OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE clauses of the INSERT statement.
The data type of the sequence matches the data type of the identity column.
GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates that the user can optionally provide an explicit value for the column.
seq_name
Defines the name of the sequence.
seq_options
Control how the sequence supplies data when requested by an application. Sequence options can be specified in any order, and none are required.
Any of the following seq_options can be specified in a blank-space separated list:
START WITH number
Specifies the start of the sequence as an integer constant. The default value is 1 for positive sequences (positive increment) and -1 for negative sequences (negative increment). (This option is valid with the CREATE SEQUENCE statement only.)
RESTART WITH number
Specifies a new start value for the sequence. (This option is valid with the ALTER SEQUENCE statement only.)
INCREMENT BY number
Specifies the increment value (positive or negative) that produces successive values of the sequence.
Default: 1
MAXVALUE number
Specifies the maximum value allowed for the sequence.
Defaults:
For positive integer sequences: 2**31-1
For positive bigint sequences: 2**63-1
For positive decimal(n) sequences: 10**(n+1)-1
For negative sequences: -1
NO MAXVALUE / NOMAXVALUE
Specifies that sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).
MINVALUE number
Specifies the minimum value allowed for the sequence.
Default:
For positive sequences: 1
For negative bigint sequences: -2**63
For negative integer sequences: -2**31
For negative decimal(n) sequences: -(10**(n+1)-1)
NO MINVALUE / NOMINVALUE
Specifies that sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).
CACHE number
Specifies the number of sequence values held in server memory. When the supply of numbers is exhausted, Vector requires a catalog access to acquire the next set.
Default: 20
NO CACHE / NOCACHE
Specifies that sequence values are not to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.
Default: CACHE 20 (when neither CACHE nor NOCACHE are specified), which ensures low catalog overhead.
Note: Gaps between two sequence values may occur: If the DBMS is restarted between two requests of a sequence value, the cached but not used values are lost and the cached values are not written back when shutting down. After restart, the Sequence Cache is empty, so the next value is taken from iisequence.
Note: In a multiple DBMS shared cache environment, sequence values may not be in chronological order. Each DBMS maintains its own Sequence Cache. No value appears twice, but the order of the requested values depends on which DBMS is returning the sequence value.
CYCLE
Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).
Default: NO CYCLE
NO CYCLE / NOCYCLE
Specifies that the sequence is not cycled when the last valid value is generated. An error is issued to the requesting transaction.
ORDER
NO ORDER / NOORDER
These options are included solely for syntax compatibility with other DBMSes that implement sequences, and are not currently supported.
Default: NOORDER
SEQUENTIAL / UNORDERED
Specifies whether values are returned sequentially or in unordered sequence. RANDOM is a synonym for UNORDERED. This option is ignored for decimal-based sequences.
Default: SEQUENTIAL
The sequence created to manage identity column values is accessible by its generated name. The generated sequence, however, cannot be explicitly dropped; instead, the identity column or table must be dropped, or the ALTER TABLE … ALTER COLUMN … DROP IDENTITY statement must be used.
For example:
GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 20)
Note: When using the COMBINE command on a table with an identity column unexpected results, such as duplicate values, may occur. When records are inserted using COMBINE rather than the SQL INSERT, the values in the identify column in the Vector table will not be synchronized with the identity values stored in the system catalogs.