SQL-J Language Reference
Page 6 of 121

ALTER TABLE statement

The ALTER TABLE statement allows you to:

  • add a column to a table
  • add a constraint to a table
  • drop an existing constraint from a table
  • add a default value for an existing column in a table
  • drop a default value for a column in a table by setting the default to null
  • override row-level locking for the table (or drop the override)
  • compress a table to reclaim unused space
  • increase the width of a VARCHAR, NVARCHAR, or BITVARYING column

Syntax

ALTER TABLE TableName
{
    ADD COLUMN ColumnDefinition |
    ADD CONSTRAINT clause |
    COMPRESS [SEQUENTIAL] |
    DROP CONSTRAINT ConstraintName [ RESTRICT | CASCADE ] |
    MODIFY { ( ColumnModifyClause ) | ColumnModifyClause } |
    SET LOCKING = { TABLE | ROW }

}

ColumnModifyClause

ColumnName DEFAULT
{
    ConstantExpression |
    NULL |
     AutoincrementDefaultDefinition

} |
ColumnName DataType |
ColumnName NULL

In the ColumnModifyClause, the AutoincrementDefaultDefinition is as defined in AutoincrementDefaultDefinition. The data type requirements for the autoincrement default are also described there.

In the ColumnModifyClause, DataType must be one of:

  • VARCHAR
  • NVARCHAR
  • BITVARYING

The only change allowed to the data type is an increase in the width.

ALTER TABLE does not affect any view that references the table being altered. This includes views that have an "*" in their SELECT list. You must drop and re-create those views if you wish them to return the new columns.

Adding Columns

The syntax for the ColumnDefinition for a new column is the same as for a column in a CREATE TABLE statement. This means that a column constraint can be placed on the new column within the ALTER TABLE ADD COLUMN statement. However, a column with a NOT NULL constraint can be added to an existing table only if the table is empty; otherwise, an exception is thrown when the ALTER TABLE statement is executed.

(See Adding Constraints for the other limitations.) Just as in CREATE TABLE, if the column definition includes a primary key constraint, the column will be made non-nullable, so an exception is thrown if you attempt to add a primary key column to a table that is not empty.

NOTE: If a table has an UPDATE trigger without an explicit column list, adding a column to that table in effect adds that column to the implicit update column list upon which the trigger is defined, and all references to transition variables are invalidated so that they pick up the new column.

Adding Constraints

ALTER TABLE ADD CONSTRAINT adds a table-level constraint to an existing table. Any supported table-level constraint type can be added via ALTER TABLE. The following limitations exist on adding a constraint to an existing table:

  • All columns included in a primary key must be non-nullable.

NOTE: When creating a primary key in a CREATE TABLE or ALTER TABLE ADD COLUMN statement, you automatically make all columns in a primary key non-nullable. ALTER TABLE ADD CONSTRAINT does not do this, so the columns it references when defining a primary key constraint must already be NOT NULL.

  • When adding a foreign key or check constraint to an existing table, Cloudscape checks the table to make sure existing rows satisfy the constraint. If any row is invalid, Cloudscape throws a statement exception and the constraint is not added.

For information on the syntax of constraints, see CONSTRAINT clause. Use the syntax for table-level constraint when adding a constraint with the ADD TABLE ADD CONSTRAINT syntax.

Compressing Tables

Use the COMPRESS clause to reclaim unused, allocated space in a table and its indexes. Typically, unused, allocated space results when a large amount of data is deleted from a table, or indexes are updated. By default, Cloudscape does not return unused space to the operating system. For example, once a page has been allocated to a table or index, it is not automatically returned to the operating system until the table or index is destroyed. ALTER TABLE COMPRESS allows you to return unused space to the operating system.

If the SEQUENTIAL keyword is not specified, Cloudscape rebuilds all indexes concurrently with the base table. Issuing a COMPRESS clause without specifying SEQUENTIAL can be memory-intensive and can potentially use a lot of temporary disk space (an amount equal to approximately two times the used space plus the unused, allocated space). This is because Cloudscape compresses the table by copying active rows to newly allocated space (as opposed to shuffling and truncating the existing space). The extra space used is returned to the operating system on COMMIT.

When SEQUENTIAL is specified, Cloudscape compresses the base table and then compresses each index sequentially. Using SEQUENTIAL uses less memory and disk space, but is more time-intensive. Use the SEQUENTIAL keyword to reduce memory and disk space usage.

ALTER TABLE COMPRESS cannot release any permanent disk space back to the operating system until a COMMIT is issued. This means that the space occupied by both the base table and its indexes cannot be released back to the operating system until a COMMIT is issued. (Only the disk space that is temporarily claimed by an external sort can be returned to the operating system prior to a COMMIT.) We recommended you issue the ALTER TABLE COMPRESS command in auto-commit mode.

NOTE: The COMPRESS command acquires an exclusive table lock on the table being compressed. All statement plans dependent on the table or its indexes are invalidated. For information on identifying unused space, see the Cloudscape Server and Administration Guide.

Dropping Constraints

ALTER TABLE DROP CONSTRAINT drops a constraint on an existing table. To drop an unnamed constraint, you must specify the generated constraint name stored in SYS.SYSCONSTRAINTS as a delimited identifier.

Dropping a primary key, unique, or foreign key constraint drops the physical index that enforces the constraint (also known as a backing index).

When you drop a primary key or unique constraint and there may be foreign key constraints referencing that primary key or unique constraint, you have two options:

  • Specifying CASCADE means Cloudscape drops all referencing foreign key constraints and their backing indexes.
  • Specifying RESTRICT means that Cloudscape throws an exception if there are any foreign keys referencing the primary key that you want to drop. The constraint is not dropped. This is the default behavior.

Modifying Columns

NEW: The ColumnModifyClause is new in Version 3.6

The ColumnModifyClause allows you to alter the named column in the following ways:

  • Adding a DEFAULT to the column or dropping a DEFAULT.

    To drop the DEFAULT, set the DEFAULT to NULL.

    See Adding or Dropping Defaults.

  • Increasing the width of a VARCHAR, NVARCHAR, or BIT VARYING data type.

    To increase the width of a column of these types, specify the data type and new size after the column name.

NOTE: You are not allowed to decrease the width or to change the data type. You are not allowed to increase the width of a column that is part of a primary or unique key referenced by a foreign key constraint or that is part of a foreign key constraint.

  • Dropping a NOT NULL constraint.

    To drop a NOT NULL constraint, specify NULL after the column name.

NOTE: You cannot drop a NOT NULL constraint from a column that is part of a primary key. You cannot add a NOT NULL constraint to a column.

NEW: The ability to drop a NOT NULL constraint with this syntax is new in Version 3.6. In previous releases you could drop a NOT NULL constraint only by adding a DEFAULT of NULL.

Adding or Dropping Defaults

You can specify a default value for a new column or add a default value to an existing column (see Modifying Columns). A default value is the value that is inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. If you add a default to a new column, existing rows in the table gain the default value in the new column. If you add a default to an existing column, existing rows in the table do not gain the default value in the new column.

To drop a default, set the default value to NULL. Doing so does not affect the values of the column for existing rows.

You can add a default of AUTOINCREMENT to an existing column in a table. If there are existing rows in the table, the values in the column for which the autoincrement default was added do not change. Note that this means that values in the column are not guaranteed to be unique (use a unique or primary key constraint to guarantee uniqueness).

You can drop a default of AUTOINCREMENT by modifying the column and setting the default to null.`

For more information about defaults, see CREATE TABLE statement.

Changing the Lock Granularity for the Table

The SET LOCKING clause allows you to override row-level locking for the specific table, if your system uses the default setting of row-level locking. (If your system is set for table-level locking, you cannot change the locking granularity to row-level locking, although Cloudscape allows you to use the SET LOCKING clause in such a situation without throwing an exception.) To override row-level locking for the specific table, set locking for the table to TABLE. If you created the table with table-level locking granularity, you can change locking back to ROW with the SET LOCKING clause in the ALTER TABLE statement. For information about why this is sometimes useful, see About the Optimizer's Selection of Lock Granularity in Tuning Cloudscape.

ALTER TABLE Examples

-- Add a new column with a column-level constraint
-- to an existing table
-- An exception will be thrown if the table
-- contains any rows
-- since the newcol will be initialized to NULL
-- in all existing rows in the table
ALTER TABLE Cities
ADD COLUMN Region VARCHAR(26)
CONSTRAINT new_constraint NOT NULL

-- Add a new unique constraint to an existing table
-- An exception will be thrown if duplicate keys are found
ALTER TABLE Countries
ADD CONSTRAINT new_unique UNIQUE(country)

-- add a new foreign key constraint to the
-- Hotels table. Each row in Hotels is checked
-- to make sure it satisfied the contraints.
-- if any rows don't satisfy the constraint, the
-- constraint is not added
ALTER TABLE HOTELS ADD CONSTRAINT City_FK
    FOREIGN KEY (city_id) REFERENCES Cities

-- Drop a primary key constraint from a table
-- You will not be able to drop this constraint if
-- there are any foreign keys referencing it
ALTER TABLE Cities DROP CONSTRAINT Cities_PK

-- Drop a primary key constraint from a table
-- and all referencing foreign keys
ALTER TABLE Cities DROP CONSTRAINT Cities_PK CASCADE

-- compress empty, unallocated space in a table
ALTER TABLE HotelAvailability COMPRESS

-- compress empty, unallocated space in a table,
-- one index at a time
ALTER TABLE HotelAvailability COMPRESS SEQUENTIAL

-- add a default value to a column
-- (existing rows are not affected)
ALTER TABLE HotelAvailability
MODIFY rooms_taken DEFAULT 1

-- drop a default value for a column
ALTER TABLE HotelAvailability
MODIFY (rooms_taken DEFAULT NULL)

-- increase the width of a VARCHAR column
ALTER TABLE Hotels
MODIFY hotel_name VARCHAR(60)

-- drop a NOT NULL CONSTRAINT
ALTER TABLE Hotels
MODIFY (hotel_name NULL)

-- change the lock granularity of a table
ALTER TABLE Hotels
SET LOCKING = TABLE

Dependency System

An ALTER TABLE statement causes all statements that are dependent on the table being altered to be recompiled before their next execution. ALTER TABLE is not allowed if there are any open cursors that reference the table being altered.