ALTER TABLE

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.

Scope

ISQL or Application Programs

SQL Syntax

  ALTER TABLE [Owner.]TableName {AddColumnSpecification
                                 AddConstraintSpecification
                                 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.
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.

SQL Syntax—AddColumnSpecification

  ADD { (ColumnDefinition [,...])
         ColumnDefinition        } [CLUSTERING ON CONSTRAINT [ConstraintID]]

Parameters—AddColumnSpecification

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.

SQL Syntax—AddConstraintSpecification

  ADD CONSTRAINT ({UniqueConstraint
                   ReferentialConstraint
                   CheckConstraint}[,...])
  [CLUSTERING ON CONSTRAINT [ConstraintID1]]

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.
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.

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

  SET TYPE {PRIVATE 
            PUBLICREAD 
            PUBLIC 
            PUBLICROW   }[RESET AUTHORITY 
                          PRESERVE AUTHORITY]

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

  SET PARTITION {PartitionName 
                 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;