ALLBASE/SQL Reference Manual (36216-90216)

360 Chapter10
SQL Statements A - D
CREATE TABLE
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.