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.