A table column element completely defines a base table column.
This is part of the table element and of the alter add element (ALTER TABLE Statement).
Syntax:
|
column_identifier |
A valid identifier for a column and must conform to the rules specified earlier in Identifiers and Column Specification. |
|
data_type |
Specifies the data type of the column according to the rules specified below in SQL Data Types and Adabas Data Types. |
|
column_constraint_element |
Optional. Specifies constraints such as UNIQUE, NOT NULL, PRIMARY KEY, etc. See Column Constraint Element. |
|
column_index_element |
Optional. Specifies an index for a column. See Column Index Element. |
|
column_physical_element |
Optional. Describes the Adabas-specific information for each column, such as the short name, suppression, etc. See Column Physical Element. |
The table column element specifies one column of a table with the attributes of this column (examples of attributes are constraints and indexes).
For each column, the column identifier and the data type definition are minimum requirements for the CREATE TABLE, CREATE CLUSTER or ALTER TABLE statements.
The column identifier must be a valid Adabas short name, else it is required to specify the Adabas short name (part of column physical element).
By default, all columns that do not have the explicit attribute NOT NULL, have implicitly the attribute NULL. There are two exceptions:
for columns which are elements of a PRIMARY KEY, a NOT NULL constraint is generated automatically during the definition, if an explicit NOT NULL constraint is not provided.
for columns in a table description having the FIXED attribute, a NOT NULL constraint is generated automatically during the definition, if an explicit NOT NULL constraint is not provided.
In CREATE TABLE DESCRIPTION and CREATE CLUSTER DESCRIPTION statements, any unspecified attributes that belong to the underlying Adabas field are automatically generated.
The column identifier must be unique within a table.
The following must be unique within a schema:
Index identifier (if specified), one will be generated when not specified.
Constraint identifier (if specified), one will be generated when not specified.
If a CREATE TABLE or CREATE CLUSTER statement is issued, then a table may only contain 926 columns. For CREATE TABLE DESCRIPTION and CREATE CLUSTER DESCRIPTION statements this limitation is lifted; you may specify elements of a PE or MU in a rotated format.
If a column has a Character data type and a precision greater than 253 characters, then the following must be true:
The column attribute NOT NULL is mandatory.
The column may not have attributes from Column Constraint Element (other than the above) or Column Index Element.
The column may not have the attribute SUPPRESSION.
You cannot combine the following attribute pairs:
SUPPRESSION and FIXED
NULL and NOT NULL
NOT NULL and DEFAULT NULL
The table below shows which parts of table column element are optional for which statements.
|
Statement |
Data Type Definition |
Column Constraint Element |
Column Index Element |
Column Default Element |
Column Physical Element |
|---|---|---|---|---|---|
|
Create Table Create Cluster |
Mandatory |
Optional |
Optional |
Optional |
Optional (1) |
|
Create Table Description Create Cluster Description |
Optional |
Optional |
Optional |
Optional |
Optional (2) |
|
Alter Table |
Mandatory |
Optional (3) |
Optional |
Optional |
Optional (4) |
(1) The SHORTNAME specification is not allowed in this statement.
(2) The SHORTNAME specification is mandatory for this statement.
(3) The NOT NULL attribute is allowed when combined with either DEFAULT ADABAS or SUPPRESSION.
(4) The only attributes allowed in this statement are NULL and SUPPRESSION.
The following elements are not part of the standard:
Column Index Element
Column Physical Element
In Column Default Element the keyword ADABAS
In Data Type Definition the keyword SEQNO
None.
Example:
The following example creates one column of the base table CRUISE.
CREATE TABLE cruise
(cruise_id NUMERIC(8) INDEX cruise1 NOT NULL UNIQUE);
Access to the underlying Adabas ISN is available via the pseudo column ISN_tablename, i.e. ISN_cruise.