 |
» |
|
|
|
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. 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.
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 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.)
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: 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 AccountingNow 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 RecFSCreating a table with a hash structure 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 WORKSpecify 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;
|
