Column Specification—Describe Column Characteristics

The column_specification in a CREATE TABLE statement describes the characteristics of the column.

This statement has the following format:

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}]

[COLLATE collation_name]

• 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).

• 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.

• [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 collation sequence to be used on the column, as one of the following:

  • UNICODE

    Specifies collation for columns containing Unicode data (nchar and nvarchar data types). This is the default collation for Unicode columns.

  • UNICODE_CASE_INSENSITIVE

    Specifies case insensitive collation for columns containing Unicode data (nchar and nvarchar data types).

  • SQL_CHARACTER

    Specifies the collation for columns containing char, C, varchar, and text data. This is the default collation for non-Unicode columns.

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, or an empty string for character and 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

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.
• 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

To specify whether a column accepts null values, specify the WITH|NOT NULL clause in the column specification.

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. This is the default for all data types except a system_maintained logical key.

• NOT NULL

  Indicates that the column does not accept nulls.

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, 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.