CREATE TABLE (Adabas Only)
Function
The CREATE TABLE statement defines a base table in the catalog and physically creates the table in the Adabas nucleus.
Syntax
CREATE TABLE TABLE NAME DEFINITION clause [ADABAS FILE DEFINIITON clause] (ADABAS COLUMN clause[,])
where
table_name_definition | Identification of the table to be created. Optionally with specification of the assigned database. See
Table Name Definition. |
adabas file definition clause | |
adabas column clause | |
Description
The CREATE TABLE statement defines the logical structure of a base table. From this logical structure the physical structure is derived, which is represented as an Adabas FDT in the related database.
Compiling the statement does not create a table. Therefore, other statements cannot reference the table
Temp Tables
Using the TEMP keyword, CREATE TABLE can create temporary tables.
Temporary tables can contain indexes; they support all the standard SQL data types, including CLOBs BLOBs, and unicode data. Temporary tables are private to the connection on which they were created and will be deleted when the connection is closed.
Limitations
The DBA can execute this statement for all users. All other users can use this statement only in a schema owned by the user.
The table specification must be unique within a schema. A table must have at least one column.
Adabas SQL Gateway adheres to the relational database theory where tables are strictly two dimensional and, therefore, periodic groups and multiple fields which are available in Adabas are not permitted for tables specified with the CREATE TABLE statement. For cases where nested data structures, i.e., MU/PE fields are involved, a special statement, CREATE CLUSTER, is provided.
SHORTNAME and UQINDEX cannot be specified in this statement. Referential constraints are not supported.
Warning:
This statement is not subject to transaction logic. An implicit COMMIT will be performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK will be performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.
ANSI Specifics
The column default value of "ADABAS" is not part of the ANSI SQL Standard.
The current <authorization identifier> must be equal to the <authorization identifier> that owns the schema identified by the implicit or explicit <schema name> of the <table name>. For SQL-2, this access rule is valid only if a <table definition> is contained in a <module>. This access rule is valid for every <table definition> with one exception. For the current <authorization identifier>:='DBA', this access rule does not apply. The <authorization identifier>:='DBA' has the privilege for a <table definition> regardless who owns the schema identified by the implicit or explicit <schema name> of the <table name>.
Examples
The following example defines and creates the table CONTRACT:
CREATE TABLE contract ( contract_id
integer index ind_contract not null unique,
price numeric (13,3) not null,
date_reservation integer,
date_booking integer,
date_cancellation integer,
date_deposit integer,
amount_deposit integer,
date_payment integer,
amount_payment numeric (13,3),
id_customer integer not null,
id_cruise integer not null );