|
|
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. 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.
ISQL or Application Programs
ALTER TABLE [Owner.]TableName {AddColumnSpecification
AddConstraintSpecification
DropConstraintSpecification
SetTypeSpecification
SetPartitionSpecification }
- [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.
- AddConstraintSpecification
allows a new constraint to be added to an existing table.
This parameter is discussed in a separate section below.
- DropConstraintSpecification
allows 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.
ADD { (ColumnDefinition [,...])
ColumnDefinition }
[CLUSTERING ON CONSTRAINT [ConstraintID]]
- ColumnDefinition
The syntax of ColumnDefinition is presented under the
CREATE TABLE statement.
- CLUSTERING ON CONSTRAINT
specifies that the named unique or referential 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.
- 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.
ADD CONSTRAINT ({UniqueConstraint
ReferentialConstraint
CheckConstraint}[,...])
[CLUSTERING ON CONSTRAINT [ConstraintID1]]
- 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.
- 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.
- 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.
DROP CONSTRAINT {(ConstraintID [,...])
ConstraintID }
- ConstraintID
is the name of the constraint optionally defined when the constraint
was defined.
SET TYPE {PRIVATE
PUBLICREAD
PUBLIC
PUBLICROW }[RESET AUTHORITY
PRESERVE AUTHORITY]
- 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.
SET PARTITION {PartitionName
DEFAULT
NONE }
- 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.
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.
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.
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;
|