![]() |
SQL-J Language Reference
|
Reference Manual |
ALTER TABLE statementThe ALTER TABLE statement allows you to:
Syntax
ALTER TABLE TableName ColumnModifyClause
ColumnName DEFAULT
} | 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: 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 ColumnsThe 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 ConstraintsALTER 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: 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.
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 TablesUse 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 ConstraintsALTER 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:
Modifying ColumnsNEW: The ColumnModifyClause is new in Version 3.6 The ColumnModifyClause allows you to alter the named column in the following ways:
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. 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 DefaultsYou 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 TableThe 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
-- Add a new unique constraint to an existing table
-- add a new foreign key constraint to the
-- Drop a primary key constraint from a table
-- Drop a primary key constraint from a table
-- compress empty, unallocated space in a table
-- compress empty, unallocated space in a table,
-- add a default value to a column
-- drop a default value for a column
-- increase the width of a VARCHAR column
-- drop a NOT NULL CONSTRAINT
-- change the lock granularity of a table Dependency SystemAn 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |