HP 3000 Manuals HP 3000 Manuals
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
[PRIVATE ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName
[PUBLIC ]
[PUBLICROW ]
{ColumnDefinition }
[LANG = TableLanguageName] ( {UniqueConstraint } [,...])
{ReferentialConstraint}
{CheckConstraint }
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[ {PartitionName}]
[IN PARTITION {DEFAULT }] [IN DBEFileSetName1]
[ {NONE }]
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.
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.
[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*((231)-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 specifies that the named unique or referential
CONSTRAINT 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 } [LANG = ColumnLanguageName]
{LongColumnType [IN DBEFileSetName2]}
[ {Constant }]
[[NOT] CASE SENSITIVE] [DEFAULT {USER }]
[ {NULL }]
[ {CurrentFunction}]
[NOT NULL [{UNIQUE } [CONSTRAINT ConstraintID]] ]
[ [{PRIMARY KEY} ] ]
[ ]
[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 } ( ColumnName [,...]) [CONSTRAINT ConstraintID]
{PRIMARY KEY}
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.[REV
BEG] (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.[REV END]
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
1. 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
2. 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
3. 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
4. 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;