HP 3000 Manuals HP 3000 Manuals
CREATE TABLE (SQL)
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.
Scope
ISQL or Application Programs
SQL Syntax--CREATE TABLE
[REV BEG]
[PRIVATE ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName [LANG = TableLanguageName]
[PUBLIC ]
[PUBLICROW ]
{ColumnDefinition }
( {[{UniqueConstraint } ]} [,...])
{[{ReferentialConstraint} [CONSTRAINT ConstraintID]]}
{[{CheckConstraint } ]}
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[IN DBEFileSetName] [REV END]
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.
[REV BEG]
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.[REV END]
[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 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.
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.
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.
UNIQUE HASH 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.
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.
ConstraintID is an optional name specified for the constraint.
If none is supplied, one is generated, as
described under "Description" below.
IN DBEFileSetName causes the index and data pages in which the
table 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 SYSTEM
DBEFileSet. For best performance, explicitly
specify a DBEFileSet which is separate from the
SYSTEM DBEFileSet.
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 DBEFileSet]}
[ {Constant }]
[LANG = ColumnLanguageName] [DEFAULT {NULL }]
[ {CurrentFunction}]
[NOT NULL [{UNIQUE } [CONSTRAINT ConstraintID]] ]
[ [{PRIMARY KEY} ] ]
[ ][...]
[REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID][...]]
[ ]
[CHECK (SearchCondition) [CONSTRAINT ConstraintID] ]
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 as
many as 255 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.
DBEFileSet specifies the DBEFileSet in which data of the
LONG column should be stored. This DBEFileSet
may be different from the tables, to reduce the
space between rows of the table in its
DBEFileSet. If a DBEFileSet is not given, 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.
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.
SQL Syntax--Unique Constraint (Table Level)
{UNIQUE } ( ColumnName [,...])
{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.
SQL Syntax--Referential Constraint (Table Level)
FOREIGN KEY ( FKColumnName [,...]) REFERENCES RefTableName
[( RefColumnName [,...])]
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.
SQL Syntax--Check Constraint (Table Level)
[CHECK (SearchCondition) [CONSTRAINT ConstraintID]]
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 of the
ALLBASE/SQL Reference Manual.) The search
condition cannot contain a subquery, host
variable, aggregate function, local variable,
procedure parameter, dynamic parameters, TID
function, current function, or USER. Refer to
"Check Constraints" in the "Using SQL" chapter
for more information on check constraints.
Description
[REV BEG]
* PUBLIC, PUBLICROW, PUBLICREAD, and PRIVATE are locking modes.[REV
END] 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.
* 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 datatype.
* 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.
[REV BEG]
* You cannot create a hash structure as a PUBLICROW table.[REV END]
* 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.
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.
Example
-----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 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
-----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.[REV BEG]
RecDB.Members and RecDB.Events are both created PUBLICROW since they
are small tables on which a large amount of concurrent access is
expected.[REV END]
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18)) IN RecFS
[REV BEG]
CREATE PUBLICROW TABLE RecDB.Members[REV END]
(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
[REV BEG]
CREATE PUBLICROW TABLE RecDB.Events[REV END]
(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