Table Column Element
Function
A table column element completely defines a base table column.
Invocation
This is part of the table element and of the alter add element (ALTER TABLE Statement).
Syntax
column_name [SQL DATA TYPE | ADABAS DATA TYPE] [COLUMN CONSTRAINT ELEMENT | COLUMN INDEX ELEMENT | COLUMN PHYSICAL ELEMENT]
where

column_name | A valid identifier for a column that 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 in Data Type Syntax.
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.

Description
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:
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.
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, CONNX generates automatically any unspecified attributes that belong to the underlying Adabas field.
Limitations
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. This limitation does not exist for CREATE TABLE DESCRIPTION and CREATE CLUSTER DESCRIPTION statements this - 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.
The following attribute pairs are incompatible:
SUPPRESSION and FIXED
NULL and NOT NULL
NOT NULL and DEFAULT NULL
The following table describes 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)

Notes:
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.
ANSI Specifics
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
Examples
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.