HP 3000 Manuals HP 3000 Manuals
ALTER TABLE
[REV BEG]
The ALTER TABLE statement is used to add one or more new columns or
constraints, to drop one or more constraints, or to reassign the table
audit partition.[REV END] This statement is also used to change the type
of table access, updatability, and locking strategies. New columns are
appended following already existing columns of a table. New column
definitions must either allow null values or provide default values if
the table is not empty. Added columns may specify constraints.
Scope
ISQL or Application Programs
SQL Syntax
{AddColumnSpecification }
{AddConstraintSpecification }
ALTER TABLE [Owner.]TableName {DropConstraintSpecification}
{SetTypeSpecification }
{SetPartitionSpecification }
Parameters--ALTER TABLE
[Owner.]TableName designates the table to be altered.
AddColumnSpecification allows a new column to be added to an existing
table. This parameter is discussed in a
separate section below.
AddConstraintSpecificationallows a new constraint to be added to an
existing table. This parameter is discussed in
a separate section below.
DropConstraintSpecificationllows an existing constraint to be dropped
from an existing table. This parameter is
discussed in a separate section below.
SetTypeSpecification allows the locking mode of the table and
related authorities to be changed. This
parameter is discussed in a separate section
below.
SetPartitionSpecification allows a table or DBEnvironment partition to be
changed.
SQL Syntax--AddColumnSpecification
ADD{(ColumnDefinition [,...] )} [CLUSTERING ON CONSTRAINT [ConstraintID]]
{Column Definition }
Parameters--AddColumnSpecification
ColumnDefinition The syntax of ColumnDefinition is presented under
the CREATE TABLE statement.
[REV BEG]
CLUSTERING ON specifies that the named unique or referential
CONSTRAINT constraint specified within the Column Definition
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.[REV END]
ConstraintID specifies the unique or referential constraint on
which clustering is to be applied. If not
specified, the primary key of the table is
assumed. The ConstraintID must be for a
constraint being added with the ALTER TABLE
statement.
SQL Syntax--AddConstraintSpecification
[REV BEG]
{UniqueConstraint }
ADD CONSTRAINT ({ReferentialConstraint} [,...] )
{CheckConstraint }
[CLUSTERING ON CONSTRAINT [ConstraintID1]]
[REV END]
Parameters--AddConstraintSpecification
UniqueConstraint defines a unique constraint being added. This
parameter is described under the CREATE TABLE
statement.
ReferentialConstraint defines a referential constraint being added.
This parameter is described under the CREATE
TABLE statement.
CheckConstraint defines a check constraint being added. This
parameter is described under the CREATE TABLE
statement.[REV BEG]
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.
ConstraintID1 specifies the unique or referential constraint
name on which clustering is to be applied. If
not specified, the primary key of the table is
assumed. ConstraintID1 must be for a constraint
being added with the ALTER TABLE statement.[REV
END]
SQL Syntax--DropConstraintSpecification
DROP CONSTRAINT{(ConstraintID [,...] )}
{ConstraintID }
Parameters--DropConstraintSpecification
ConstraintID is the name of the constraint optionally defined
when the constraint was defined.
SQL Syntax--SetTypeSpecification
{PRIVATE }
SET TYPE {PUBLICREAD} [RESET AUTHORITY ]
{PUBLIC } [PRESERVE AUTHORITY]
{PUBLICROW }
Parameters--SetTypeSpecification
PRIVATE enables the table to be used by only one
transaction at a time. Locks are applied at the
table level. This is the most efficient option
for tables that do not need to be shared because
ALLBASE/SQL spends less time managing locks.
If RESET AUTHORITY is specified, the option
automatically revokes all authorities on the
table from PUBLIC. Otherwise, the authority on
the table remains unchanged.
PUBLICREAD enables the table to be read by concurrent
transactions, but allows no more than one
transaction at a time to update the table. Locks
are applied at the table level.
If RESET AUTHORITY is specified, the option
automatically issues GRANT SELECT on
Owner.TableName to PUBLIC, and revokes all other
authorities on the table from PUBLIC. Otherwise,
the authority on the table remains unchanged.
PUBLIC enables the table to be read and updated by
concurrent transactions. The locking unit is a
page. A transaction locks a page in share mode
before reading it and in exclusive mode before
updating it.
If RESET AUTHORITY is specified, the option
automatically issues GRANT ALL on Owner.TableName
to PUBLIC. Otherwise, the authority on the table
remains unchanged.
PUBLICROW enables the table to be read and updated by
concurrent transactions. The locking unit is a
row. A transaction locks a row in share mode
before reading it and in exclusive mode before
updating it.
If RESET AUTHORITY is specified, the option
automatically issues GRANT ALL on Owner.TableName
to PUBLIC. Otherwise, the authority on the table
remains unchanged.
RESET AUTHORITY is used to indicate that the authority on the
table should be changed to reflect the new table
type. If not specified, the authority on the
table remains unchanged.
PRESERVE AUTHORITY is used to indicate that the authority currently
in effect on the table should be preserved. This
is the default.
SQL Syntax--SetPartitionSpecification
{PartitionName}
SET PARTITION {DEFAULT }
{NONE }
Parameters--SetPartitionSpecification
PartitionName specifies the new partition of the table.
DEFAULT specifies the new partition of the table to be
the default partition of the DBEnvironment. If
the default partition number is later changed,
that change will automatically be recorded the
next time an INSERT, UPDATE, or DELETE operation
is executed on the table. If the default
partition is NONE at that time, audit logging of
the operation is not done.
NONE specifies that the table is no longer in any
partition. No further audit logging will be done
on the table.
Description
* Unless the table is currently empty, you cannot specify the NOT
NULL attribute for any new columns unless you specify a default
value.
* If no DEFAULT clause is given for an added column, 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.
* All rows currently in the table are updated with the default value
for any new column which specifies default values.
* The ALTER TABLE statement can invalidate stored sections.
* Character strings are accepted as date/time default values.
* If an added constraint is violated when it is defined, an error
message is immediately issued and the ALTER TABLE statement has no
effect.
* A unique constraint referenced by a FOREIGN KEY cannot be dropped
without first dropping the referential constraint.
* Constraints being added in AddConstraintSpecification must be on
existing columns of the table.
* The ALTER TABLE statement can be used to change the type of an
existing table. Changing the type of a table redefines the
locking strategy that ALLBASE/SQL uses when the table is accessed.
You can decide whether to use page or row level locking for your
applications.
* No other transaction can access the table until the transaction
that issued the ALTER TABLE statement has committed.
* The type of a table is changed permanently when you issue a COMMIT
WORK statement.
* When altering the type of an existing table, you can also specify
the option to preserve existing authority on the table or change
the authority to the default for the new table type. If you
specify RESET AUTHORITY, the following changes are made to the
table authority:
Table 10-2. Changes to Table Authority in ALTER TABLE
--------------------------------------------------------------------------
- Old Table Type - New Table Type - Changes to Authority -
--------------------------------------------------------------------------
- PRIVATE - PUBLIC - Grant ALL to PUBLIC -
- - PUBLICROW - Grant ALL to PUBLIC -
- - PUBLICREAD - Grant SELECT to PUBLIC -
--------------------------------------------------------------------------
- PUBLICREAD - PUBLIC - Grant ALL to PUBLIC -
- - PUBLICROW - Grant ALL to PUBLIC -
- - PRIVATE - Revoke ALL from PUBLIC -
--------------------------------------------------------------------------
- PUBLIC - PUBLICROW - No change -
- - PUBLICREAD - Revoke ALL from PUBLIC -
- - - Grant SELECT to PUBLIC -
- - PRIVATE - Revoke ALL from PUBLIC -
--------------------------------------------------------------------------
- PUBLICROW - PUBLIC - No change -
- - PUBLICREAD - Revoke ALL from PUBLIC -
- - - Grant SELECT to PUBLIC -
- - PRIVATE - Revoke ALL from PUBLIC -
--------------------------------------------------------------------------
* To indicate that a table is in no partition, the partition NONE
can be specified.
* The PartitionName specified must be one previously defined in a
CREATE PARTITION statement, must be the DEFAULT partition, or must
be specified as NONE.
* Changing the partition number of the table causes all future audit
logging on the table to use the new partition number. Past audit
log records will not be altered to reflect a change in a table's
partition number; that is, the effect of this statement is not
retroactively applied to existing log records. If NONE was
specified, there will be no more audit logging done on this table
(until another ALTER TABLE SET PARTITION statement is issued on
the table).
* When specifiying CLUSTERING ON CONSTRAINT, an error is returned if
the table is already clustered on a constraint or index or if the
table is hashed.
* Adding a clustered constraint does not affect the physical
placement of rows already in the table.
* See syntax for the CREATE TABLE and CREATE INDEX statements for
more information on clustering.
Authorization
You can issue this statement if you have ALTER or OWNER authority for the
table or if you have DBA authority.
To define added referential constraints, the table owner must have
REFERENCES authority on the referenced table and referenced columns, own
the referenced table, or have DBA authority.
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
Two new columns, ShippingWeight and PartDescription, are added to table
PurchDB.Parts. ShippingWeight must be greater than 0.
ALTER TABLE PurchDB.Parts
ADD (ShippingWeight DECIMAL(6,3) CHECK (ShippingWeight > 0)
CONSTRAINT Check_Weight,
PartDescription CHAR(40))
A constraint is added to table PurchDB.Parts to ensure that the sales
price is greater than $100.
ALTER TABLE PurchDB.Parts
ADD CONSTRAINT CHECK (SalesPrice > 100.) CONSTRAINT Check_Price
A column named DiscountPercent is added to table PurchDB.OrderItems, with
a default value of 0 percent.
ALTER TABLE PurchDB.OrderItems
ADD (DiscountPercent FLOAT DEFAULT 0)
The constraint named Check_Price is dropped.
ALTER TABLE PurchDB.Parts
DROP CONSTRAINT Check_Price
The type of a table is changed:
ALTER TABLE PurchDB.OrderItems
SET TYPE PUBLICROW
The table's partition is modified to be partition PartsPart2.
ALTER TABLE PurchDB.Parts
SET PARTITION PartsPart2;
No more audit logging will be done on the table.
ALTER TABLE PurchDB.Parts
SET PARTITION NONE;