When a base table is created or modified (CREATE TABLE, ALTER TABLE), the column definition defines the name and the attributes of a column.
SESAM/SQL distinguishes between atomic and multiple columns. In an atomic column, exactly one value can be stored in each row. In a multiple column, several values of the same type can be stored in each row. A multiple column is made up of a number of column elements. In the case of a single column, a single value is stored for each row.
To incorporate BLOBs in base tables, you will need REF columns. These are defined using the FOR REF clause.
A base table can contain a maximum of 26134 columns of any data type except VARCHAR and NVARCHAR. It can contain up to 1000 VARCHAR and/or NVARCHAR columns. The restrictions that apply to CALL DML tables are described on "Column definitions".
column_definition ::= column { data_type [ default ] | FOR REF( table ) }
[[CONSTRAINT integrity_constraint_name ] col_constraint ] ...
[ call_dml_clause ]
default ::= DEFAULT
| |
alphanumeric_literal | national_literal | numeric_literal | time_literal | CURRENT_TIME(3) | LOCALTIME(3) | CURRENT_TIMESTAMP(3) | LOCALTIMESTAMP(3) | USER | CURRENT_USER | SYSTEM_USER | NULL | REF( tabelle ) | |
|
call_dml_clause ::= CALL DML call_dml_default [ call_dml_symb_name ]
column
Name of the column. The column name must be unique within the base table.
data_type
Data type of the column.
FOR REF(table)
Defines a column containing references to BLOB values. This clause allows you to incorporate BLOBs in “normal” base tables. BLOB values are stored in BLOB tables. Information on defining a BLOB table can be found in the section "CREATE TABLE - Create base table". BLOB objects, tables and REF values are explained briefly in the section "Concept of the SESAM CLI". Detailed information on their structure can be found in the “ Core manual”.
The column is assigned the data type CHAR(237).
Its default value is the class REF value. The structure of REF values is described below.
table must not contain the database name (catalog).
REF(table)
Class REF value which identifies the overall class of the BLOB values of a BLOB table. When a REF column is created, it is assigned this value as the default. This is determined by specifying the name of the BLOB table. Due to the syntax of the column definition, therefore, it is neither practical nor possible to specify a default value for the REF column at this point.
A REF value essentially has the following structure:
ss/tt?UID=uuuu&OID=nn
ss is the unqualified name of the BLOB table's schema, excluding the database name.
tt is the unqualified name of the BLOB table, excluding the schema and database name.
uuu is the unique BLOB ID consisting of 32 hexadecimal digits. In the case of the class REF value, all the digits are 0.
nn is the number of the BLOB in the BLOB table. In the case of the class REF value, this number is 0.
default
Defines an SQL default value that is entered in the column if a row is inserted or updated and no value or the default value is specified for the column.
column cannot be a multiple column.
column cannot be a CALL DML column.
default must conform to the assignment rules for default values (see section "Default values for table columns").
The default is evaluated when a row is inserted or updated and the default value is used for column.
default omitted:
There is no SQL default value.
The NULL value is entered in columns without a NOT NULL constraint.
[CONSTRAINT integrity_constraint_name] column_constraint
Defines an integrity constraint for the column. Integrity constraints cannot be specified for multiple columns.
[CONSTRAINT integrity_constraint_name] column_constraint omitted:
No column constraint defined.
CONSTRAINT integrity_constraint_name
Assigns a name to the integrity constraint. The unqualified name of the integrity constraint must be unique within the schema. You can qualify the name of the integrity constraint with a database and schema name. The database and schema name must be the same as the database and schema name of the base table for which the integrity condition is defined.
CONSTRAINT integrity_constraint_name omitted:
The integrity constraint is assigned a name according to the following pattern:
UN integrity_constraint_number
PK integrity_constraint_number
FK integrity_constraint_number
CH integrity_constraint_number
where UN stands for UNIQUE, PK for PRIMARY KEY, FK for FOREIGN KEY and CH for CHECK. integrity_constraint_number is a 16-digit number. The NOT NULL constraint is stored as a check constraint.
column_constraint
Indicates an integrity constraint that the column must satisfy.
call_dml_clause
The CALL DML clause ensures compatibility with SESAM/SQL V1.x. The CALL DML clause can only be specified for CALL DML tables, but not for columns used for the primary key. In this case, SESAM/SQL assigns both the call_dml_default and the call_dml_symb_name
call_dml_clause omitted:
The column definition is valid for either an SQL table or for the primary key of a CALL DML table. In the case of an SQL table, the CREATE TABLE or ALTER TABLE statement in which the column definition occurs cannot include a CALL DML clause.
call_dml_default
Indicates the non-significant value of a column as an alphanumeric literal.
call_dml_default corresponds to the non-significant value in SESAM/SQL Version 1.x.
call_dml_symb_name
Symbolic name of the column.
call_dml_symb_name corresponds to the symbolic attribute name in
SESAM/SQL Version 1.x.
call_dml_symb_name omitted:
call_dml_symb_name is assigned by the system.
Special considerations for CALL DML tables
The following restrictions must be observed when creating column definitions for CALL DML tables:
Only the data types CHAR, NUMERIC, DECIMAL, INTEGER and SMALLINT are permitted.
No default value can be defined for the column with DEFAULT. The default value FOR REF is not permitted either.
The table must contain exactly one primary key restraint as the column or table constraint.
The table constraint defines a compound primary key and must be given a name that corresponds to the name of the compound primary key in SESAM/SQL V1.x.
The column name must be different to the integrity constraint name of the table constraint since this name is used as the name of the compound primary key.
A column that is not a primary key must have a CALL DML clause.
Examples of column definitions
This example shows part of the CREATE TABLE statement used to create the ORDERS table of the ORDERCUST database.
 |
|
|
This example shows the CREATE TABLE statement used to create the ITEM_CAT table of the ORDERCUST database. This table contains two REF columns.
 |
|
|