ALTER TABLE statement

Use this statement to modify a table definition, disable dependent views, or enable a table to take part in Replication Server replication.

Syntax 
ALTER TABLE [owner.]table-name { alter-clause, ... }
alter-clause :
ADD create-clause
| ALTER column-name column-alteration
| ALTER [ CONSTRAINT constraint-name ] CHECK ( condition )
| DROP drop-object
| RENAME rename-object
| table-alteration

create-clause :
column-name [ AS ] column-data-type [ new-column-attribute ... ]
| table-constraint
| PCTFREE integer

column-alteration :
 { column-data-type | alterable-column-attribute } [ alterable-column-attribute ... ]
| SET COMPUTE ( compute-expression )
| ADD [ constraint-name ] CHECK ( condition )
| DROP { DEFAULT | COMPUTE | CHECK | CONSTRAINT constraint-name }

drop-object :
column-name
| CHECK
| CONSTRAINT constraint-name
| UNIQUE [ CLUSTERED ] ( index-columns-list )
| FOREIGN KEY fkey-name
| PRIMARY KEY

rename-object :
new-table-name
| column-name TO new-column-name
| CONSTRAINT constraint-name TO new-constraint-name 

table-alteration :
PCTFREE DEFAULT
| REPLICATE { ON | OFF }
| [ NOT ] ENCRYPTED

new-column-attribute :
NULL
| DEFAULT default-value
| COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX
| IDENTITY
| COMPUTE ( expression )
| column-constraint

table-constraint :
[ CONSTRAINT constraint-name ] {
   CHECK ( condition )
   | UNIQUE [ CLUSTERED | NONCLUSTERED ] ( column-name [ ASC | DESC ], ... ) 
   | PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] ( column-name [ ASC | DESC ], ... )
   | foreign-key
  }

column-constraint :
[ CONSTRAINT constraint-name ] {
   CHECK ( condition )
   | UNIQUE [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
   | PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
   | REFERENCES table-name [ ( column-name ) ] 
      [ MATCH [ UNIQUE] { SIMPLE | FULL } ]  
      [ actions ][ CLUSTERED | NONCLUSTERED ]
   | NOT NULL
  }

alterable-column-attribute :
 [ NOT ] NULL
| DEFAULT default-value
| [ CONSTRAINT constraint-name ] CHECK { NULL | ( condition ) }
| [ NOT ] COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX

default-value :
 special-value
| string
| global variable 
| [ - ] number
| ( constant-expression )
| built-in-function ( constant-expression )
| AUTOINCREMENT 
| GLOBAL AUTOINCREMENT [ ( partition-size ) ]
| NULL
| TIMESTAMP
| UTC TIMESTAMP
| LAST USER
| USER

special-value :
CURRENT { 
   DATABASE 
   | DATE
   | REMOTE USER 
   | TIME
   | TIMESTAMP 
   | UTC TIMESTAMP
   | USER 
   | PUBLISHER }

foreign-key :
[ NOT NULL ] FOREIGN KEY [ role-name ] 
   [ ( column-name [ ASC | DESC ], ... )
   REFERENCES table-name 
   [ ( pkey-column-list ) ] 
   [ MATCH [ UNIQUE] { SIMPLE | FULL } ]
   [ actions ] [ CHECK ON COMMIT ] [ CLUSTERED ]
   [ FOR OLAP WORKLOAD ]

actions :
[ ON UPDATE action ] [ ON DELETE action ]

action :
CASCADE | SET NULL | SET DEFAULT | RESTRICT
Syntax 2 - Disabling view dependencies 
ALTER TABLE [owner.]table-name {
 DISABLE VIEW DEPENDENCIES
}
Parameters

  • Adding clauses   The following section explains the clauses used for adding definition for a column or table:

    • ADD column-name [ AS ] column-data-type [ new-column-attribute ... ] clause   Use this syntax to add a new column to the table, specifying the data type and attributes for the column. For more information about what data type to specify, see SQL data types.

    • NULL and NOT NULL clauses   Use this clause to specify whether to allow NULLs in the column. With the exception of bit type columns, new columns allow NULL values. Bit type columns automatically have the NOT NULL constraint applied when created.

    • DEFAULT clause   Sets the default value for the column. All rows in the column are populated with this value. For information about possible default values, see CREATE TABLE statement.

    • column-constraint clause   Use this clause to add a constraint to the column. With the exception of CHECK constraints, when a new constraint is added, the database server validates existing values to confirm that they satisfy the constraint. CHECK constraints are enforced only for operations that occur after the table alteration is complete. Possible column constraints include:

      • CHECK clause   Use this subclause to add a check condition for the column.

      • UNIQUE clause   Use this subclause to specify that values in the column must be unique, and whether to create a clustered or nonclustered index.

      • PRIMARY KEY clause   Use this subclause to make the column a primary key, and whether to use a clustered index. For more information about clustered indexes, see Using clustered indexes.

      • REFERENCES clause   Use this subclause to add or alter a reference to another table, to specify how matches are handled, and to specify whether to use a clustered index. For more information about clustered indexes, see Using clustered indexes.

      • NULL and NOT NULL clauses   Use this clause to specify whether to allow NULL values in the column. By default, NULLs are allowed.

    • COMPRESSED clause   Use this clause to compress the column.

    • INLINE and PREFIX clauses   When storing BLOBs (character and binary data types only), use the INLINE and PREFIX clauses to specify how much of a BLOB, in bytes, to keep within a row. For more information, see the INLINE and PREFIX clauses in CREATE TABLE statement.

    • INDEX and NO INDEX clauses   Use this clause to specify whether to build indexes on large BLOBs in this column. For more information about how to use this clause, see the corresponding section for the [NO] INDEX clause in the CREATE TABLE statement.

    • IDENTITY clause   This clause is equivalent to AUTOINCREMENT, and is provided for compatibility with T-SQL. See the description for AUTOINCREMENT in CREATE TABLE statement.

    • COMPUTE clause   Use this clause to ensure that the value in the column reflects the value of expression. For more information about what is allowed for the COMPUTE clause, see CREATE TABLE statement.

    • ADD table-constraint clause   Use this clause to add a table constraint. Table constraints place limits on what columns in the table can hold. When adding or altering table constraints, the optional constraint name allows you to modify or drop individual constraints. Following is a list of the table constraints you can add.

      • UNIQUE   Use this subclause to specify that values in the columns specified in column-list must be unique, and, optionally, whether to use a clustered index. For more information about this constraint, see CREATE TABLE statement.

      • PRIMARY KEY   Use this subclause to add or alter the primary key for the table, and specify whether to use a clustered index. The table must not already have a primary key that was created by the CREATE TABLE statement or another ALTER TABLE statement. For more information about this constraint, see CREATE TABLE statement.

        For more information about clustered indexes, see Using clustered indexes.

      • foreign-key   Use this subclause to add a foreign key as a constraint. If you use a subclause other than ADD FOREIGN KEY with the ALTER TABLE statement on a table with dependent materialized views, the ALTER TABLE statement fails. For all other clauses, you must disable the dependent materialized views and then re-enable them when your changes are complete.

        For more information about this constraint, see CREATE TABLE statement.

    • ADD PCTFREE clause   Specify the percentage of free space you want to reserve in each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation. A free space percentage of 0 specifies that no free space is to be left on each page—each page is to be fully packed. A high free space percentage causes each row to be inserted into a page by itself. If PCTFREE is not set, or is dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB, and up, page size). The value for PCTFREE is stored in the ISYSTAB system table. When PCTFREE is set, all subsequent inserts into table pages use the new value, but rows that were already inserted are not affected. The value persists until it is changed. The PCTFREE specification can be used for base, global temporary, or local temporary tables.

  • Altering clauses   The following section explains the clauses used for altering the definition for a column or table:

    • ALTER column-name column-alteration clause   Use this clause to change attributes for the specified column. If a column is contained in a unique constraint, a foreign key, or a primary key, you can change only the default for the column. However, for any other change, you must delete the key or constraint before the column can be modified. Following is a list of the alterations you can make. For further information about these attributes, see CREATE TABLE statement.

    • column-data-type clause   Use this clause to alter the length or data type of the column. If necessary, the data in the modified column is converted to the new data type. If a conversion error occurs, the operation will fail and the table is left unchanged. You cannot reduce the size of a column. For example, you cannot change a column from a VARCHAR(100) to a VARCHAR(50).

    • [ NOT ] NULL clause   Use this clause to change whether NULLs are allowed in the column. If NOT NULL is specified, and the column value is NULL in any of the existing rows, then the operation fails and the table is left unchanged.

    • CHECK NULL   Use this clause to delete all check constraints for the column.

    • DEFAULT clause   Use this clause to change the default value for the column.

    • DEFAULT NULL clause   Use this clause to remove the default value for the column.

    • [ CONSTRAINT constraint-name ] CHECK { NULL | ( condition ) } clause   Use this clause to add a CHECK constraint on the column.

    • [ NOT ] COMPRESSED clause   Use this clause to change whether the column is compressed.

    • INLINE and PREFIX clauses   Use the INLINE and PREFIX clauses with columns that contain BLOBs to specify how much of a BLOB, in bytes, to keep within a row. For more information about how to set the INLINE and PREFIX values, see the corresponding sections for the INLINE and PREFIX clauses in CREATE TABLE statement.

    • INDEX and NO INDEX clauses   Use this clause to specify whether to build indexes on large BLOBs in this column. For more information about how to use this clause, see the corresponding section for the [NO] INDEX clause in the CREATE TABLE statement.

    • SET COMPUTE clause   Use this clause to change the expression associated with the computed column. The values in the column are recalculated when the statement is executed, and the statement fails if the new expression is invalid. For more information about what is allowed for the COMPUTE expression, see CREATE TABLE statement.

    • ALTER CONSTRAINT constraint-name CHECK clause   Use this clause to alter a named check constraint for the table.

  • Dropping clauses   The following section explains the DROP clauses:

    • DROP DEFAULT   Drops the default value set for the table or specified column. Existing values do not change.

    • DROP COMPUTE   Removes the COMPUTE attribute for the specified column. This statement does not change any existing values in the table.

    • DROP CHECK   Drops all CHECK constraints for the table or specified column. DELETE CHECK is also accepted.

    • DROP CONSTRAINT constraint-name   Drops the named constraint for the table or specified column. DELETE CONSTRAINT is also accepted.

    • DROP column-name   Drops the specified column from the table. DELETE column-name is also accepted. If the column is contained in any index, unique constraint, foreign key, or primary key, then the index, constraint, or key must be deleted before the column can be deleted. This does not delete CHECK constraints that refer to the column.

    • DROP UNIQUE ( column-name ... )   Drop the unique constraints on the specified column(s). Any foreign keys referencing this unique constraint are also deleted. DELETE UNIQUE ( column-name ... ) is also accepted.

    • DROP FOREIGN KEY fkey-name   Drop the specified foreign key. DELETE FOREIGN KEY fkey-name is also accepted.

    • DROP PRIMARY KEY   Drop the primary key. All foreign keys referencing the primary key for this table are also deleted. DELETE PRIMARY KEY is also accepted.

  • Renaming clauses   The following section explains the clauses used for renaming parts of a column or table definition:

    • RENAME new-table-name   Change the name of the table to new-table-name. Note that any applications using the old table name must be modified, as necessary. After the renaming operation succeeds, foreign keys with ON UPDATE or ON DELETE actions must be dropped and re-created, as the system-created triggers used to implement these actions will continue to refer to the old name.

    • RENAME column-name TO new-column-name   Change the name of the column to the new-column-name. Note that any applications using the old column name will need to be modified, as necessary. After the renaming operation succeeds, foreign keys with ON UPDATE or ON DELETE actions must be dropped and re-created, as the system-created triggers used to implement these actions will continue to refer to the old name.

    • RENAME CONSTRAINT constraint-name TO new-constraint-name   Change the name of the constraint to the new-constraint-name.

  • table-alteration clauses   Use this clause to alter the following table attributes.

    • PCTFREE DEFAULT   Use this clause to change the percent free setting for the table to the default (200 bytes for a 4 KB, and up, page size).

    • REPLICATE { ON | OFF }   Use this clause to change whether the table is included during replication. When a table has REPLICATE ON, all changes to the table are sent to Replication Server for replication. The replication definitions in Replication Server are used to decide which table changes are sent to other sites.

    • [ NOT ] ENCRYPTED   Use this clause to change whether the table is encrypted. To encrypt a table, table encryption must already be enabled on the database. The table is encrypted using the encryption key and algorithm specified at database creation time. See Enabling table encryption in the database. After encrypting a table, any data for that table that was in temporary files or the transaction log prior to encryption still exists in unencrypted form. To address this, restart the database to remove the temporary files. Run the Backup utility (dbbackup) with the -o option, or use the BACKUP statement, to back up the transaction log and start a new one. See Backup utility (dbbackup) or BACKUP statement.

      When table encryption is enabled, table pages for the encrypted table, associated index pages, temporary file pages, and transaction log pages containing transactions on encrypted tables are encrypted.

  • DISABLE VIEW DEPENDENCIES clause   Use this clause to disable dependent regular views. Dependent materialized views are not disabled; you must disable each dependent materialized view by executing an ALTER MATERIALIZED VIEW ... DISABLE statement. See ALTER MATERIALIZED VIEW statement.

Remarks

The ALTER TABLE statement changes table attributes (column definitions, constraints, and so on) in an existing table.

The database server keeps track of object dependencies in the database. Alterations to the schema of a table may impact dependent views. Also, if there are materialized views that are dependent on the table you are attempting to alter, you must first disable them using the ALTER MATERIALIZED VIEW ... DISABLE statement. For information about view dependencies, see View dependencies.

You cannot use ALTER TABLE on a local temporary table.

ALTER TABLE is prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time-consuming, and the database server does not process other requests referencing the table while the statement is being processed.

For more information about using the CLUSTERED option, see Using clustered indexes.

If you alter a column that a text index defined as IMMEDIATE REFRESH is built on, the text index is immediately rebuilt. If the text index is defined as AUTO REFRESH or MANUAL REFRESH, the text index is rebuilt the next time it is refreshed.

When you execute an ALTER TABLE statement, the database server attempts to restore column permissions on dependent views that are automatically recompiled. Permissions on columns that no longer exist in the recompiled views are lost.

Permissions

Must be one of the following:

  • The owner of the table.

  • A user with DBA authority.

  • A user who has been granted ALTER permission on the table.

ALTER TABLE requires exclusive access to the table.

Global temporary tables cannot be altered unless all users that have referenced the temporary table have disconnected.

Cannot be used within a snapshot transaction. See Snapshot isolation.

Side effects

Automatic commit.

A checkpoint is carried out at the beginning of the ALTER TABLE operation, and further checkpoints are suspended until the ALTER operation completes.

Once you alter a column or table, any stored procedures, views, or other items that refer to the altered column may no longer work.

If you change the declared length or type of a column, or drop a column, the statistics for that column are dropped. For information about how to generate new statistics, see Updating column statistics to improve optimizer performance.

See also
Standards and compatibility

  • SQL/2003   ADD COLUMN is a core feature. Other clauses are vendor extensions or implementation of specific, named extensions to SQL/2003.

Example

The following example adds a new timestamp column, TimeStamp, to the Customers table.

ALTER TABLE Customers 
   ADD TimeStamp AS TIMESTAMP DEFAULT TIMESTAMP;

The following example drops the new timestamp column, TimeStamp that you added in the previous example.

ALTER TABLE Customers
DROP TimeStamp;

The Street column in the Customers table can currently hold up to 35 characters. To allow it to hold up to 50 characters, execute the following: 

ALTER TABLE Customers
ALTER Street CHAR(50);

The following example adds a column to the Customers table, assigning each customer a sales contact.

ALTER TABLE Customers
ADD SalesContact INTEGER
REFERENCES Employees ( EmployeeID )
ON UPDATE CASCADE
ON DELETE SET NULL;

This foreign key is constructed with cascading updates and is set to NULL on deletes. If an employee has their employee ID changed, the column is updated to reflect this change. If an employee leaves the company and has their employee ID deleted, the column is set to NULL.

The following example creates a foreign key, FK_SalesRepresentative_EmployeeID2, on the SalesOrders.SalesRepresentative column, linking it to Employees.EmployeeID: 

ALTER TABLE GROUPO.SalesOrders
   ADD CONSTRAINT FK_SalesRepresentative_EmployeeID2
   FOREIGN KEY ( SalesRepresentative ) 
   REFERENCES GROUPO.Employees (EmployeeID);
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1