Column Specifications
The column_specification in a CREATE TABLE statement describes the characteristics of the column.
The column_specification has the following syntax:
column_name datatype
[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT]
[WITH NULL | NOT NULL|
[[CONSTRAINT constraint_name] column_constraint
{[CONSTRAINT constraint_name] column_constraint}]
where column_constraint is one or more of the following:
UNIQUE [WITH constraint_with_ clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES[schema.]table_name[(column_name)]
[WITH constraint_with_clause]
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).
[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT]
Specifies whether the column is mandatory, as described in
DEFAULT Clause.
WITH NULL | NOT NULL
Specifies whether the column accept nulls, as described in
NULL Clause.
[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.
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 requires an entry (is mandatory).
WITH DEFAULT
Indicates that if no value is provided, 0 is inserted for numeric and money columns, or an empty string for character and date columns.
[WITH] DEFAULT default_spec | USER | NULL
Inserts the specified default value if the user or program does not provide a value for the column. The default value must be compatible with the data type of the column.
USER
Specifies the session's current user ID as the default value.
NULL
Specifies NULL as the default value for nullable columns.
If the DEFAULT clause is omitted, the column default depends on whether the column is nullable. Nullable columns default to nulls, and non-nullable columns are mandatory.
The following is an example of the DEFAULT option:
create table dept( dname character(10),
budget decimal default 100000.00,
creation date default date('01/01/94'));
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 or the declared length of the column, whichever is shorter.
• For fixed length string columns, if the column is wider than the default value, the default value is padded with blanks to the full 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 date columns, the default value must be a valid date specified using the date() function. If the time zone is omitted, the time zone defaults to the time zone of the user inserting the row.
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 as the default value.
NOT NULL
Indicates that the column does not accept nulls. If the DEFAULT clause is omitted or NOT DEFAULT is specified, the column is mandatory.
With|Not Null and With|Not Default 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, a null is inserted.
NOT NULL
The column is mandatory and does not accept nulls, which is typical for primary key columns.
WITH NULL WITH DEFAULT
The column accepts null values. If no value is provided, the default value is inserted.
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 default value is inserted. (The specified default value cannot be NULL.)
NOT NULL NOT DEFAULT (or NOT NULL)
The column is mandatory and does not accept nulls, which is typical for primary key columns.