Column Attributes
(column_attributes) 
A column definition can contain the column name (column_name) and column attributes (column_attributes).
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>
Explanation
A CONSTRAINT definition constraint_definition defines a condition that all column values in the column defined by the column definition column_definition must fulfill.
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 |
LONG data type: you may only specify NOT NULL or a DEFAULT specification as a column attribute for LONG columns.
UNIQUE
The UNIQUE column attribute determines the uniqueness of column values (see also CREATE INDEX statement).
[PRIMARY] KEY
The [PRIMARY] KEY and UNIQUE column attributes must not be used together in a column definition.
If the [PRIMARY] KEY column attribute 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.
See also:
Restrictions for SQL Statements
If a table is defined without a key column, the database system implicitly creates a key column SYSKEY CHAR(8) BYTE. 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; that is, 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 with the DEFAULT specification STAMP can be defined.
NOT NULL
· NOT NULL must not be used together with the DEFAULT specification NULL.
· NOT NULL WITH DEFAULT defines a default value 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) BYTE; VARCHAR(n) BYTE |
X'00' |
0 |
|
DATE |
|
TIME |
|
TIMESTAMP |
|
FALSE |