CREATE TABLE

The CREATE TABLE statement defines a table. It also defines the locking strategy that ALLBASE/SQL uses automatically when the table is accessed and in some cases automatically issues a GRANT statement. It can also define the storage structure of the table and restrictions or defaults placed on values which the table's columns can hold. You can also use this statement to assign a table to a partition for audit logging purposes.

Scope

ISQL or Application Programs

SQL Syntax--CREATE TABLE

CREATE [PRIVATE PUBLICREAD PUBLIC PUBLICROW] TABLE [Owner.] TableName [LANG = TableLanguageName] ( { ColumnDefinition UniqueConstraint ReferentialConstraint CheckConstraint } [,...]) [ UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages CLUSTERING ON CONSTRAINT [ConstraintID] ] [IN PARTITION { PartitionName DEFAULT NONE }] [IN DBEFileSetName1]

Parameters--CREATE TABLE

PRIVATE

enables the table to be used by only one transaction at a time. This is the most efficient option for tables that do not need to be shared because ALLBASE/SQL can spend less time managing locks.

This option is in effect by default; grants are not automatically issued.

PUBLICREAD

enables the table to be read by concurrent transactions, but allows no more than one transaction at a time to update the table.

This option automatically issues a statement GRANT SELECT ON TableName TO PUBLIC. This gives any user with CONNECT authority the ability to read the table. To change this grant, use the REVOKE statement and the GRANT statement. The locking strategy remains unchanged, even if you change the grant.

PUBLIC

enables the table to be read and updated by concurrent transactions. In general, a transaction locks a page in share mode before reading it and in exclusive mode before updating it.

This option automatically issues the statement GRANT ALL ON TableName TO PUBLIC. This gives any user with CONNECT authority the ability to read and modify the table as well as to alter the table and create indexes on it. To change this grant, use the REVOKE statement and the GRANT statement. The locking strategy remains unchanged, even if you change the grant.

PUBLICROW

enables the table to be read and updated by concurrent transactions. The locking unit is a row (tuple) in PUBLICROW tables. In general, a transaction locks a row in share mode before reading it and in exclusive mode before updating it. For small tables with small rows, concurrency can be maximized by using the PUBLICROW type.

[Owner.]TableName

is the name to be assigned to the new table. Two tables cannot have the same owner name and table name.

You can specify the owner of the new table if you have DBA authority. If you do not have DBA authority, you can specify the owner as the name of any group to which you belong. If you do not specify the owner name, your DBEUserID, schema authorization name, procedure owner, or the ISQL SET OWNER name becomes the owner of the new table. For more information, refer to the section "Default Owner Rules" in the chapter "Using ALLBASE/SQL."

TableLanguageName

specifies the language for the new table. This name must be either NATIVE-3000 or the language of the DBEnvironment. The default is the language of the DBEnvironment.

ColumnDefinition

defines an individual column in a table. Each table must have at least one column. The syntax for a CREATE TABLE column definition is presented separately in another section below.

UniqueConstraint

defines a uniqueness constraint for the table. Each table can have multiple unique constraints, but can have only one specifying PRIMARY KEY. The syntax for a UniqueConstraint is presented separately in another section below.

ReferentialConstraint

defines a referential constraint of this table with respect to another (or the same) table. The referencing table (this one) and the referenced table (the other one) satisfy the constraint if, and only if each row in the referencing table contains either a NULL in a referencing column, or values in the rows of the referencing columns equal the values in the rows of the referenced columns. The syntax of a ReferentialConstraint is presented separately in another section below.

CheckConstraint

defines a check constraint for the table. A table can have multiple check constraints. The syntax for a check constraint is presented separately in another section below.

UNIQUE HASH ON

specifies a hash structure for the table. Only UNIQUE HASH structures may be created, and updates on hash key columns are not permitted (you must first delete, then insert the row with the new key value).

HashColumnName

specifies a column defined in the table that is to participate in the hash key of this table.

PrimaryPages

specifies the number of pages used as primary hash buckets. The minimum is 1 and the maximum is determined by the formula 16*((2³¹)-2072). For good results, use a prime number.

HASH ON CONSTRAINT

specifies that the named unique constraint be managed through the use of hash table storage. The unique constraint's columns become the hash key columns.

ConstraintID

is an optional name specified for the constraint. If none is supplied, one is generated, as described under "Description" below.

IN PARTITION

specifies what partition the table will be in for the purposes of audit logging.

PartitionName

specifies the partition for the table.

DEFAULT

specifies that the default partition of the database will be used. The number associated with the default partition is determined at the time the INSERT, UPDATE, or DELETE is executed on the table. If the default partition is NONE at that time, audit logging of the operation is not done. Any change to the default partition number occurring in a START DBE NEWLOG statement alters the partition number that audit logging uses on tables that are in the default partition.

NONE

specifies that this table is assigned to no partition, and so will have no audit logging done on it.

CLUSTERING ON CONSTRAINT

specifies that the named unique or referential constraint be managed through a clustered index structure rather than nonclustered. The unique constraint's unique column list, or referential constraint's referencing column list, becomes the clustered key.

IN DBEFileSetName1

causes the index and data pages in which table information is stored to be allocated from DBEFiles associated with the specified DBEFileSet. (Names of available DBEFileSets are recorded in the SYSTEM.DBEFILESET view.) If a DBEFileSet name is not specified, the table is created in the default TABLESPACE DBEFileSet. (Refer to syntax for the SET DEFAULT DBEFILESET statement.)

You can create a nonhash table in an empty DBEFileSet, but cannot INSERT any rows or create any indexes for the table until the DBEFileSet has DBEFiles associated with it.

You cannot create a hash structure in an empty DBEFileSet.

SQL Syntax--Column Definition

ColumnName { ColumnDataType LongColumnType [IN DBEFileSetName2] } [ LANG = ColumnLanguageName ] [ [ NOT ] CASE SENSITIVE ] [ DEFAULT { Constant USER NULL CurrentFunction }] [ NOT NULL [ { UNIQUE PRIMARY KEY } [CONSTRAINT ConstraintID]] REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID] [...] CHECK (SearchCondition) [CONSTRAINT ConstraintID] [IN DBEFileSetName3]] [...]

Parameters--Column Definition

ColumnName: is the name to be assigned to one of the columns in the new table. No two columns in the table can be given the same name. You can define a maximum of 1023 columns in a table.
ColumnDataType: indicates what type of data the column can contain. Some data types require that you include a length. See the "Data Types" chapter for the data types that can be specified.
LongColumnType: specifies a LONG data type for the new column. At most 40 columns with a LongColumnType may be defined in a single table.
DBEFileSetName2: specifies the DBEFileSet where long column data is to be stored. This DBEFileSet may be different from that of the table. If a DBEFileSet is not specified, the LONG data is stored in the DBEFileSet containing the table.
ColumnLanguageName: specifies the language for the column. This can only be specified for CHAR or VARCHAR columns. This name must be either NATIVE-3000 or the language of the DBEnvironment. The default is the language of the DBEnvironment.
CASE SENSITIVE: indicates that upper and lower case letters stored in the column are not considered equivalent. If the column is defined as NOT CASE SENSITIVE, then its upper and lower case letters are considered equivalent. The default is CASE SENSITIVE. This clause is allowed only with CHAR and VARCHAR columns.
DEFAULT: specifies the default value to be inserted for this column. The default can be a constant, NULL, or a date/time current function The data type of the default value must be compatible with the data type of the column. DEFAULT cannot be specified for LONG data type columns.
NOT NULL: means the column cannot contain null values. If NOT NULL is specified, any statement that attempts to place a null value in the column is rejected. However, if atomicity is set to row level, only the NULL row receives the error and the statement halts.
UNIQUE | PRIMARY KEY: specifies a unique constraint placed on the column. The table level constraint { UNIQUE | PRIMARY KEY } (ColumnName) is equivalent. See the discussion on table level unique constraints below.
REFERENCES: specifies a Referential Constraint placed on the column. This is equivalent to the table level constraint FOREIGN KEY (ColumnName) REFERENCES RefTableName [(RefColumnName)]. See the discussion on table level referential constraint below.
CHECK: specifies a check constraint placed on the column.
SearchCondition: specifies a boolean expression that must not be false. The result of the boolean expression may be unknown if a value in the expression is NULL. See the discussion on a table level check constraint below. In addition, for a column definition check constraint, the only column the search condition can reference is ColumnName.
ConstraintID: is an optional name specified for the constraint. If none is supplied, one is generated, as described under "Description" below.
DBEFileSetName3: specifies the DBEFileSet to be used for storing the section associated with the check constraint. If not specified, the default SECTIONSPACE DBEFileSet is used. (Refer to syntax for the SET DEFAULT DBEFILESET statement.)

SQL Syntax--Unique Constraint (Table Level)

{ UNIQUE PRIMARY KEY } ( ColumnName [,...]) [CONSTRAINT ConstraintID]

Parameters--Unique Constraint (Table Level)

UNIQUE: Each ColumnName shall identify a column of the table, and the same column shall not be identified more than once. Also, NOT NULL shall be specified for each column in this unique constraint column list.
PRIMARY KEY: In addition to the rules for the UNIQUE option, PRIMARY KEY may only be specified once in a table definition. It provides a shorthand way of referencing its particular unique constraint column list in a referential constraint.
ColumnName [,...]: is the unique constraint column list, or key list, of the constraint. No two unique constraints may have identical column lists. The maximum number of columns in a unique column list is 15. None of the columns may be a LONG data type.
ConstraintID: is an optional name specified for the constraint. If none is supplied, one is generated, as described under "Description" below.

SQL Syntax--Referential Constraint (Table Level)

FOREIGN KEY ( FKColumnName [,...]) REFERENCES RefTableName [( RefColumnName [,...])] [CONSTRAINT ConstraintID]

Parameters--Referential Constraint (Table Level)

FKColumnName [,...]: identifies the referencing column list. Each referencing column shall be a column defined in the referencing table, and the same column name shall not be identified more than once. The number of referencing and referenced columns would be the same. The maximum number of columns in a referencing column list is 15. None of the columns may be a LONG data type.
RefTableName: identifies the base table being referenced. If no RefColumnName list follows this, the base table must contain a PRIMARY KEY unique constraint with the correct number of columns, each of the correct data type.
RefColumnName [,...]: identifies the referenced column list. This column list must be identical to a unique constraint column list of the referenced table.
ConstraintID: is an optional name specified for the constraint. If none is supplied, one is generated, as described under "Description" below.

SQL Syntax--Check Constraint (Table Level)

CHECK (SearchCondition) [CONSTRAINT ConstraintID ] [IN DBEFileSetName3]

Parameters--Check Constraint (Table Level)

CHECK: specifies a check constraint.
SearchCondition: specifies a boolean expression for the check constraint. The result of the boolean expression must not be false for any row of the table. The result may be unknown if a column that is part of the expression is NULL. The search condition may only contain LONG columns if they are within long column functions. (Refer to long column functions in the "Expressions" and "Data Types" chapters.) The search condition cannot contain a subquery, host variable, aggregate function, built-in variable, local variable, procedure parameter, dynamic parameter, TID function, current function, or USER. Refer to the chapter, "Constraints, Procedures, and Rules," for more information on check constraints.
ConstraintID: is an optional name specified for the constraint. If none is supplied, one is generated, as described under "Description" below.
DBEFileSetName3: specifies the DBEFileSet to be used for storing the section associated with the check constraint. If not specified, the default SECTIONSPACE DBEFileSet is used. (Refer to syntax for the SET DEFAULT DBEFILESET statement.)

Description

PUBLIC, PUBLICROW, PUBLICREAD, and PRIVATE are locking modes. They define the type of locking ALLBASE/SQL uses automatically when the table is accessed. The LOCK TABLE statement can be used to override automatic locking during any transaction, if the override is to a more restrictive lock. If no locking mode is specified, PRIVATE is assumed. For complete information on locking, refer to the chapter "Concurrency Control through Locks and Isolation Levels."
For nonhash tables, CREATE TABLE simply enters the new table's definition into the system catalog. Until you insert a row into the new table, the table does not occupy any storage. For hash tables, the number of primary pages is allocated at CREATE TABLE time.
Data and index values of columns defined as NOT CASE SENSITIVE are not converted to upper case when stored. However, during comparison, sorting, and indexing operations, upper and lower case letters are considered equivalent. If a case sensitive column is compared to a column that is not case sensitive, both columns are treated as case sensitive. When defining a referential constraint, the case sensitivity of the referenced and referencing columns must match.
The NOT CASE SENSITIVE clause has no effect if the character set does not differentiate between upper and lower case, such as Chinese.
Upper and lower case extended characters are treated as equivalent. They are compared to the collation table of a specific language regardless of case.
If no DEFAULT clause is given for a column in the table, an implicit DEFAULT NULL is assumed. Any INSERT statement, which does not include a column for which a default has been declared, causes the default value to be inserted into that column for all rows inserted.
For a CHAR column, if the specified default value is shorter in length than the target column, it is padded with blanks. For a CHAR or VARCHAR column, if the specified default value is longer than the target column, it is truncated.
For a BINARY column, if the specified default value is shorter in length than the target column, it is padded with zeroes. For a BINARY or VARBINARY column, if the specified default value is longer than the target column, it is truncated.
If a constraint is defined without a ConstraintID, one is generated of the following form:
SQLCON_uniqueid
where the uniqueid is unique across all constraints. You cannot define a constraint starting with SQLCON_. All constraint names must be unique for a given owner, regardless of which table they are in.
Unique constraints are managed through the use of B-tree indexes unless the constraint is named and its name is referenced in the HASH ON CONSTRAINT clause. If the name is referenced in the CLUSTERING ON CONSTRAINT clause, the B-tree index is clustered.
Referential constraints are managed through the use of virtual indexes. A virtual index is created by ALLBASE/SQL. Virtual indexes can be clustered with respect to the referencing columns' values if the constraint is named in the CLUSTERING ON CONSTRAINT clause.
The behavior by which integrity constraints are enforced is determined by the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements. Refer to the discussion of these statements in this chapter for more information.
Unique constraint indexes use space in this table's DBEFileSet; but referential constraint virtual indexes use space in the referenced table's DBEFileSet.
If the HASH or CLUSTERING ON CONSTRAINT clause is used without a constraint name, the PRIMARY KEY of the table is used. If a PRIMARY KEY is not defined, an error results.
At most 15 columns may be used in a unique or referential constraint key. The maximum length of the index key for unique or referential constraints is obtained from the following formula:
(NumberOfColumns + 3)* 2 + SumColumnLengths + 10 = 1024
An extra 2 bytes must be added for each column that allows NULLS or is a VARCHAR data type.
The data types of the corresponding columns in a referential constraint's referencing and referenced column lists must be the same with the following exceptions. CHAR and VARCHAR are allowed to refer to each other, as are the pairs BINARY and VARBINARY, and NATIVE CHAR and NATIVE VARCHAR. DECIMAL columns must exactly match in precision and scale. SMALLINT, INTEGER, FLOAT, and REAL references cannot refer to a data type other than their same data type. LONG columns may not be used in integrity constraints.
You can use the same set of foreign key columns to reference two different primary keys.
The maximum size of a hash key is the same as a user-defined index key, which is determined in the following formula:
(NumberOfHashColumns+2)* 2 + SumKeyColumnLengths + 8 <= 1024
An extra 2 bytes must be added for each column that allows NULLS or is a VARCHAR datatype.
At most 16 columns are allowed in a hash structure key.
A hash structure may not be dropped, except by dropping the table upon which it is defined with the DROP TABLE statement.
You cannot create a hash structure as a PUBLICROW table.
If the table is created with a HASH structure, enough empty data and mixed DBEFiles must exist to contain the primary pages for the hash table data at the time the table is created. Primary pages for hash tables cannot be placed in DBEFile0, an index DBEFile, or a nonempty DBEFile. Similarly, data for nonhash tables cannot be placed in a DBEFile containing primary pages for hash tables.
The partition must be already created by the CREATE PARTITION statement, it must be the default partition, or it must be specified as NONE.
The partition number of a table's partition is recorded in any audit logging generated on that table. Audit logging is done on any INSERT, UPDATE, or DELETE performed on a table while the DBEnvironment is enabled for DATA audit logging, unless the table is in the partition NONE.
Audit logging is not done on any LONG column data for the table.
If no partition is specified, the table is placed in the DEFAULT partition.
To specify that a table is in no partition, the partition NONE can be specified.
Partitions can be created and tables placed in them without DATA audit logging being enabled for a DBEnvironment. However, the partition information is only used in audit log records. Thus, partition information is not utilized until the DBEnvironment has DATA audit logging enabled through the START DBE NEWLOG statement.
If the IN DBEFileSetName1 clause is specified for the table or the IN DBEFileSetName2 clause is specified for a long column, but the table owner does not have TABLESPACE authority for the specified DBEFileSet, a warning is issued and the default TABLESPACE DBEFileSet is used instead. (Refer to syntax for the GRANT statement and the SET DEFAULT DBEFILESET statement.)
If the IN DBEFileSetName3 clause is specified for a check constraint, but the table owner does not have SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the default SECTIONSPACE DBEFileSet is used instead. (Refer to syntax for the GRANT statement and the SET DEFAULT DBEFILESET statement.)

Authorization

You must have RESOURCE or DBA authority to use this statement. To define referential constraints, the table owner must have REFERENCES authority on the referenced table and referenced columns, own the referenced table, or have DBA authority for the life of the referential constraint. The REVOKE, DROP GROUP, and REMOVE FROM GROUP statements are not permitted if they remove REFERENCES authority from the table's owner until the referential constraint or table is dropped or ownership is transferred to someone else.

To specify a DBEFileSetName for a long column, the table owner must have TABLESPACE authority on the referenced DBEFileSet.

To specify a DBEFileSetName for a check constraint, the section owner must have SECTIONSPACE authority on the referenced DBEFileSet.

Examples

Creating and accessing tables

This public table is accessible to any user or program that can start a DBE session. It is also accessible by concurrent transactions.

CREATE PUBLIC TABLE PurchDB.SupplyPrice

                    (PartNumber  CHAR(16) NOT NULL,

                    VendorNumber  INTEGER  NOT NULL,

                    VendPartNumber CHAR(16) NOT NULL,

                    UnitPrice  DECIMAL(10,2),

                    DeliveryDays  SMALLINT DEFAULT 0,

                    DiscountQty  SMALLINT) 

                 {{IN PARTITION PartsPart}}

            IN PurchFS;



REVOKE ALL PRIVILEGES ON PurchDB.SupplyPrice FROM PUBLIC



GRANT SELECT,UPDATE ON Purch.DB.SupplyPrice TO Accounting

Now only the DBA and members of authorization group Accounting can access the table. Later, the accounting department manager is given control.

TRANSFER OWNERSHIP OF PurchDB.SupplyPrice TO MGR@ACCOUNT

Creating a table using constraints and LONG columns

In this example, the tables are created with the PUBLIC option so as to be accessible to any user or program that can start a DBE session. RecDB.Clubs defines those clubs which can have members and hold events, as shown by the constraint Members_FK. RecDB.Members defines those members who can have events for certain clubs, as shown by constraint Events_FK. The LONG column Results is used to hold a text file containing the results of a completed event. No date can be entered for an event that is prior to the current date. RecDB.Members and RecDB.Events are both created PUBLICROW since they are small tables on which a large amount of concurrent access is expected.

CREATE PUBLIC TABLE RecDB.Clubs

                    (ClubName CHAR(15) NOT NULL

                    PRIMARY KEY CONSTRAINT Clubs_PK,

                    ClubPhone SMALLINT,

                    Activity CHAR(18)) 

                 NOT CASE SENSITIVE

                 IN RecFS



CREATE PUBLICROW TABLE RecDB.Members

                       (MemberName CHAR(20) NOT NULL,

                       Club CHAR(15) NOT NULL,

                       MemberPhone SMALLINT,

                       PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,

                       FOREIGN KEY (Club) REFERENCES RecDB.Clubs (ClubName)

                       CONSTRAINT Members_FK) 

                    IN RecFS



CREATE PUBLICROW TABLE RecDB.Events

                       (SponsorClub CHAR(15),

                       Event CHAR(30),

                       Date DATE DEFAULT CURRENT_DATE,

                       CHECK (Date >= '1990-01-01') CONSTRAINT Events_Date_Ck,

                       Time TIME,

                       Coordinator CHAR(20),

                       Results LONG VARBINARY(10000) IN LongFS,

                       FOREIGN KEY (Coordinator, SponsorClub)

                       REFERENCES RecDB.Members (MemberName, Club)

                       CONSTRAINT Events_FK) 

                    IN RecFS

Creating a table with a hash structure

BEGIN WORK

Statements to create a DBEFile and add it to a DBEFileSet should be in the same transaction as the statement to create the hash structure. This makes it impossible for other transactions to use the new DBEFile for hashing before the hash structure is created.

CREATE DBEFILE PurchHashF1 WITH PAGES = 120,

               NAME = 'PurchHF1', 

               TYPE = TABLE

   ADD DBEFILE PurchHashF1 

 TO DBEFILESET PurchFS



CREATE PUBLIC TABLE PurchDB.Vendors

                    (VendorNumber INTEGER  NOT NULL,

                    VendorName CHAR(30) NOT NULL,

                    ContactName CHAR(30),

                    PhoneNumber CHAR(15),

                    VendorStreet CHAR(30) NOT NULL,

                    VendorCity  CHAR(20) NOT NULL,

                    VendorState  CHAR(2) NOT NULL,

                    VendorZipCode  CHAR(10) NOT NULL,

                    VendorRemarks  VARCHAR(60) )

     UNIQUE HASH ON (VendorNumber) PAGES = 101 

                 IN PurchFS

COMMIT WORK

Specify a DBEFileSet for a Check Constraint in the Column Definition

CREATE PUBLIC TABLE RecDB.Events

                    (SponsorClub CHAR(15),

                    Event CHAR(30),

                    Date DATE DEFAULT CURRENT_DATE,

                  {{CHECK (Date >= '1990-01-01') CONSTRAINT Events_Date_Ck}}

                  {{IN RecFS,}}

                    Time TIME,

                    Coordinator CHAR(20),

                    Results LONG VARBINARY(10000) IN LongFS,

                    FOREIGN KEY (Coordinator, SponsorClub)

                    REFERENCES RecDB.Members (MemberName, Club)

                    CONSTRAINT Events_FK)

                 IN RecFS;