ALTER TABLE statement

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

ALTER TABLE [owner.]table-name {
 DISABLE VIEW DEPENDENCIES
}

Adding clauses The following section explains the clauses used for adding columns or table constraints to a table:
- ADD column-name [ AS ] column-data-type [ new-column-attribute ... ] clause Use this clause 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 they are 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 specify 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. See Using clustered indexes.
  - MATCH clause Use this subclause to control what is considered a match when using a multi-column foreign key. It also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately. For the list of match types you can specify, see MATCH clause, CREATE TABLE statement.
  - 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 CREATE TABLE statement.
- IDENTITY clause This clause is equivalent to AUTOINCREMENT, and is provided for compatibility with Transact-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 data 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.
    You can specify a MATCH subclause to control what is considered a match when using a multi-column foreign key. It also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately. For the list of match types you can specify, see MATCH clause, CREATE TABLE statement.
    
    For more information about adding a foreign key relationship to a table, 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 or larger 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 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. 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 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. Any applications using the old column namemust 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 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.
  ALTER TABLE ... RENAME CONSTRAINT constraint-name TO new-constraint-name, when used for an RI constraint, only renames the constraint, not the underlying index or, if applicable, the foreign key role name. If you want to rename the underlying index or the role name, use the ALTER INDEX statement. See ALTER INDEX statement.
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).
- [ 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 before 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.

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.

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.

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.

SQL/2008 ALTER TABLE is a core feature. In the SQL/2008 standard, ADD COLUMN and DROP COLUMN are supported as core features, as are ADD CONSTRAINT and DROP CONSTRAINT. ALTER [COLUMN] is SQL feature F381, as is the ability to add, modify, or drop a DEFAULT value for a column. In SQL/2008, altering the data type of a column is performed by specifying the SET DATA TYPE clause, which is SQL language feature F382. Conversely, SQL Anywhere supports modifying a column's data type through the ALTER clause directly.
Other clauses supported by SQL Anywhere, including ALTER CONSTRAINT, RENAME, PCTFREE, ENCRYPTED, and DISABLE MATERIALIZED VIEW, are vendor extensions. Support for extensions to column definitions, and column and table constraint definitions, are vendor extensions to SQL/2008 or are specific optional features of SQL/2008.
Transact-SQL ALTER TABLE is supported by Adaptive Server Enterprise. Adaptive Server Enterprise supports the ADD COLUMN and DROP COLUMN clauses, in addition to ADD CONSTRAINT and DROP CONSTRAINT. Adaptive Server Enterprise uses MODIFY rather than the keyword ALTER for the ALTER clause. Adaptive Server Enterprise uses the REPLACE clause for altering a column's DEFAULT value. In Adaptive Server Enterprise, ALTER TABLE is also used to enable/disable triggers for a specific table, a feature that is not supported in SQL Anywhere.

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);

ALTER TABLE statement

Syntax 1 - Altering an existing table

Syntax 2 - Disabling view dependencies

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example