|
|
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.
ISQL or Application Programs
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]
- 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
n-computer 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 31)-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.
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] ][...]
- 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 n-computer 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.)
{UNIQUE
PRIMARY KEY}( ColumnName [,...]) [CONSTRAINT ConstraintID]
- 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.
FOREIGN KEY (FKColumnName [,...])
REFERENCES RefTableName [( RefColumnName [,...])]
[CONSTRAINT ConstraintID]
- 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.
CHECK (SearchCondition) [CONSTRAINT ConstraintID]
[IN DBEFileSetName3]
- 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.)
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.)
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.
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 MgrAcct
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;
|