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 null | not null]
[with default default_spec | with default | not default]
column_name
Assigns a valid name to the column.
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).
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
not default
Indicates the column is mandatory (requires an entry).
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, and the current date for ANSI date columns.
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
For boolean columns, valid default values include FALSE or TRUE.
The following is an example of using the default clause:
create table dept(dname char(10),
location char(10) not null with default 'NY',
creation date not null with default '01/01/03',
budget money not null with 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.
• 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.
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 default [default_spec]]
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.