ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 361
SQL Statements A - D
CREATE TABLE
• 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.