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]
[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}]
[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).
Notes:
• For char and varchar columns, the column specification is in number of bytes (not number of characters). The maximum number of bytes is 32,000, which may not be 32,000 characters if multi-byte characters are used.
• For nchar or nvarchar columns, the column specification is the number of UCS-2 characters. The maximum number of characters is 16,000, which uses 32,000 bytes. Characters are 2 bytes each.
DEFAULT clause
Specifies whether the column is mandatory, as described in
Default Clause.
NULL clause
Specifies whether the column accept nulls, as described in
Null Clause.
[MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }]
Displays the column with the specified mask characteristic unless the user has the UNMASK privilege. The mask characteristic defines how to display the masked data:
BASIC – Fills the width of the column with asterisks
NULL – NULL
0 – 0
‘ ‘ – blank
Note: INGRESDATE columns can only be masked as NULL. If a column is defined as INGRESDATE NOT NULL, it cannot be masked, since masked as NULL is not allowed for NOT NULL columns. In contrast, ANSIDATE columns can be masked as BASIC (displayed as 1970-01-01) and NULL.
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.
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.
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.
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.
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 following constants:
– USER
– CURRENT_USER
– SYSTEM_USER
– TIMESTAMP_UNIX (Can be applied to int4, int8, and character columns that can hold the maximum number of digits generated by an int4 value).
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, an empty string for Ingres date 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/03',
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. Since Unicode characters may require up to 4 bytes of UTF-8, the number of characters that can be stored in a default value may be reduced if using code points that are more than 1 byte each. For byte, varbyte, and long byte type columns, the limit is 749 bytes.
• 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.
• For user-defined data types (UDTs), the default value must be specified using a literal that makes sense to the UDT. A default value cannot be specified for a logical key column.
• For spatial data types, you cannot specify a default value.
Null Clause
The WITH|NOT NULL clause in the column specification specifies whether a column accepts null values.
This clause has the following format:
WITH NULL | NOT NULL
WITH NULL
Indicates that the column accepts nulls. If no value is supplied by the user, null is inserted. WITH NULL is the default for all data types except a SYSTEM_MAINTAINED logical key.
NOT NULL
Indicates that the column does not accept nulls.
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.
SYSTEM_MAINTAINED Logical Keys
SYSTEM_MAINTAINED logical key columns are assigned values by the DBMS Server, and cannot be assigned values by applications or end users. The following restrictions apply to logical keys specified as WITH SYSTEM_MAINTAINED:
• The only valid default clause is WITH DEFAULT. If the default clause is omitted, WITH DEFAULT is assumed.
• The only valid null clause is NOT NULL. If a column constraint or null clause is not specified, NOT NULL is assumed.
Sequence Defaults
The default value for a column can be the next value in a sequence.
The Sequence-operator can be in either form NEXT VALUE FOR sequence or sequence.NEXTVAL, where sequence is a sequence name, optionally specified as owner.sequence.
For example:
CREATE SEQUENCE lineitemsequence;
CREATE TABLE lineitem
(itemid INTEGER NOT NULL,
itemseq INTEGER NOT NULL WITH DEFAULT NEXT VALUE FOR lineitemsequence);
INSERT INTO lineitem (itemid) VALUES (4);
INSERT INTO lineitem VALUES (8, NEXT VALUE FOR lineitemsequence);
INSERT INTO lineitem VALUES (15, lineitemsequence.nextval);
INSERT INTO lineitem VALUES (16, 23);
INSERT INTO lineitem (itemid) VALUES (42);
Note: If the schema of the sequence providing the default value is not specified, then it defaults to the schema (owner) of the table. The user that inserts a row into the table that uses the sequence must have been
granted (see
GRANT (privilege)) the NEXT privilege to be able to retrieve the value from the sequence.
Sequence defaults are allowed on numeric columns only (integer, bigint, float, decimal). The column data type need not match the sequence data type exactly; sequence values are coerced to column values, if necessary.
If a row is inserted with some auto-incrementing columns defaulted, all relevant sequences are incremented once before the row is inserted, and the new sequence values are used whenever referenced in the row. This means that if two columns reference the same sequence for their default, they receive the same value, not two successive values.
Sequence defaulting is allowed in all contexts where column defaulting is allowed: INSERT, UPDATE (using SET COLUMN=DEFAULT), and COPY (including bulk copy).
Columns added or altered with the ALTER TABLE statement cannot use sequence defaults.
Defining a sequence default does not prevent you from explicitly assigning a value to the column upon insert or update. An explicitly assigned value may collide with a defaulted value from the sequence.
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
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence.
An identity column provides 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 is part of the column specification and has the following format:
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
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 optional seq_name defines the name of the sequence.
The optional
seq_options define how the sequence supplies data when requested by an application. All sequence options available for the
CREATE SEQUENCE are valid, except the data type option. The data type of the sequence matches the data type of the identity column.
For example:
GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 20)
GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates that the user can optionally provide an explicit value for the column.
The optional seq_name defines the name of the sequence.
The optional
seq_options define how the sequence supplies data when requested by an application. All sequence options available for the
CREATE SEQUENCE are valid, except the data type option. The data type of the sequence matches the data type of the identity column.
The sequence created to manage identity column values is accessible by its generated name and can be used as any other sequence, with CURRENT VALUE and NEXT VALUE operators. 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.
More information: