HP 3000 Manuals HP 3000 Manuals
Chapter 3 Syntax and System Catalog Changes for Row Level Locking
ALTER TABLE (SQL)
[REV BEG]
The ALTER TABLE statement is used to change table type, to add one or
more new columns or constraints, or to drop one or more constraints. 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 }
ALTER TABLE [Owner.]TableName {AddConstraintSpecification }
{DropConstraintSpecification}
{TableTypeDefinition }
Parameters
[Owner.]TableName designates the table to be altered.
AddColumnSpecification allows a new column to be added to an
existing table:
ADD{(ColumnDefinition [,...] )}
{Column Definition }
The syntax of ColumnDefinition is
presented under the CREATE TABLE
statement.
AddConstraintSpecification allows a new constraint to be added to an
existing table:
{ {UniqueConstraint [CONSTRAINT ConstraintID] } }
{({ReferentialConstraint [CONSTRAINT ConstraintID]} [,...] )}
ADD CONSTRAINT{ {CheckConstraint [CONSTRAINT ConstraintID] } }
{{UniqueConstraint [CONSTRAINT ConstraintID] } }
{{ReferentialConstraint [CONSTRAINT ConstraintID]} }
{{CheckConstraint [CONSTRAINT ConstraintID] } }
DropConstraintSpecification allows an existing constraint to be
dropped from an existing table using the
following syntax:
DROP CONSTRAINT{(ConstraintID [,...] )}
{ConstraintID }
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.
ConstraintID is the name of the constraint. The
ConstraintID is optional when adding the
constraint, but required when dropping
the constraint. This parameter is
described under the CREATE TABLE
statement.
TableTypeDefinition allows the locking mode of the table and
related authorities to be changed. A
TableTypeDefinition has the following
syntax:
{PRIVATE }
SET TYPE {PUBLICREAD} [RESET AUTHORITY ]
{PUBLIC } [PRESERVE AUTHORITY]
{PUBLICROW }
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 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.
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.
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 references 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 3-1. 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 -
--------------------------------------------------------------------------
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, for the life of the referential constraint.
Example
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 is dropped.
DROP CONSTRAINT Check_Price
The type of a table is changed:
ALTER TABLE PurchDB.OrderItems
SET TYPE PUBLICROW
[REV END]