Column Attributes (column_attributes)

A column definition can contain the column name (column_name) and column attributes (column_attributes).

Structure

Syntax Syntax

<column_attributes> ::=
  [<key_or_not_null_spec>]
  [<default_spec>] 
  [UNIQUE]
  [<constraint_definition>]
  [REFERENCES <referenced_table>
    [(<referenced_column>)]
    [<delete_rule>]]

<key_or_not_null_spec> ::=
  [PRIMARY] KEY
| NOT NULL [WITH DEFAULT]

<referenced_table> ::=
  <table_name>

<referenced_column> ::=
  <column_name>

End of the code.

Explanation

A CONSTRAINT definition constraint_definition defines a condition that must be fulfilled by all the column values in the columns defined by the column_definition.

Specifying [REFERENCES <referenced_table> [(<referenced_column>)] [<delete_rule>] has the same effect as specifying the referential CONSTRAINT definition FOREIGN KEY [<referential_constraint_name>] (<referencing_column>) REFERENCES <referenced_table> [(<referenced_column>,...)] [<delete_rule>]

referenced_table

referenced_column

Referenced table

Referenced column

Data type LOB: you may only specify NOT NULL or a DEFAULT specification as a column attribute for LOB columns.

The [PRIMARY] KEY and UNIQUE column attributes must not be used together in a column definition.

[PRIMARY] KEY

If the column attribute [PRIMARY] KEY is specified, the CREATE TABLE statement must not contain a key definition.

If the KEY column attribute is specified, this column is part of the key of a table and is called the key column. The database system ensures that the key values in a table are unique. To improve performance, the key should start with key columns which can assume many different values and which are to be used frequently in conditions with the "=" operator.

If a table is defined without a key column, the database system creates a key column SYSKEY CHAR(8) BYTE implicitly. This column is not visible with a SELECT *. However, it can be specified explicitly and has then the same function as a key column. The SYSKEY column can be used to obtain unique keys generated by the database system. The keys are in ascending order, thus reflecting the order of insertion in the table. The key values in the SYSKEY column are only unique within a table. This means the SYSKEY column in two different tables may contain the same values. If a unique key is desired across the entire database system, a key column of the data type CHAR(8) BYTE can be defined with the DEFAULT specification STAMP.

NOT NULL

NOT NULL must not be used together with the DEFAULT specification NULL.

NOT NULL WITH DEFAULT defines a default value (DEFAULT) that is dependent on the data type of the column. NOT NULL WITH DEFAULT must not be used with any of the DEFAULT specifications.

Column Data Type	DEFAULT Value
CHAR (n) VARCHAR(n)	' '
CHAR(n) BYTE VARCHAR(n) BYTE	X'00'
FIXED(p,s) INT SMALLINT FLOAT(p)	0
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
BOOLEAN	FALSE

UNIQUE

The UNIQUE column attribute determines the uniqueness of column values (see also CREATE INDEX statement).

More Information

Restrictions for SQL Statements