Modifies a table definition or disables dependent views.
Syntax 1 - Altering an existing tableALTER 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 : [ NOT ] 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 ) | ( sequence-expression ) | built-in-function( constant-expression ) | AUTOINCREMENT | GLOBAL AUTOINCREMENT [ ( partition-size ) ]
special-value : CURRENT DATABASE | CURRENT DATE | CURRENT TIME | [ CURRENT ] TIMESTAMP | CURRENT PUBLISHER | CURRENT REMOTE USER | [ CURRENT ] USER | [ CURRENT ] UTC TIMESTAMP | LAST USER | NULL
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 dependenciesALTER TABLE [owner.]table-name { DISABLE VIEW DEPENDENCIES }
ParametersAdding 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.
NULL and NOT NULL clauses Use this clause to specify whether to allow NULLs in the column. By default, new columns allow NULL values. BIT type columns automatically have the NOT NULL constraint applied when they are created, but you can declare a BIT type column to be nullable.
DEFAULT clause If a DEFAULT value is specified, it is used as the value for the column in any INSERT statement that does not specify a value for the column. If no DEFAULT value is specified, it is equivalent to DEFAULT NULL.
Following is a list of possible values for DEFAULT:
special-value You use one of several specials values in the DEFAULT clause.
[ CURRENT ] TIMESTAMP Provides a way of indicating when each row in the table was last modified. When a column is declared with DEFAULT TIMESTAMP, a default value is provided for inserts, and the value is updated with the current date and time of day whenever the row is updated.
To provide a default value on insert, but not update the column whenever the row is updated, use DEFAULT CURRENT TIMESTAMP instead of DEFAULT TIMESTAMP.
Columns declared with DEFAULT TIMESTAMP contain unique values, so that applications can detect near-simultaneous updates to the same row. If the current TIMESTAMP value is the same as the last value, it is incremented by the value of the default_timestamp_increment option.
You can automatically truncate TIMESTAMP values in SQL Anywhere based on the default_timestamp_increment option. This is useful for maintaining compatibility with other database software that records less precise timestamp values.
The global variable @@dbts returns a TIMESTAMP value representing the last value generated for a column using DEFAULT TIMESTAMP.
[ CURRENT ] UTC TIMESTAMP Provides a way of indicating when each row in the table was last modified. When a column is declared with DEFAULT UTC TIMESTAMP, a default value is provided for inserts, and the value is updated with the current Coordinated Universal Time (UTC) whenever the row is updated.
To provide a default value on insert, but not update the column whenever the row is updated, use DEFAULT CURRENT UTC TIMESTAMP instead of DEFAULT UTC TIMESTAMP.
The behavior of this default is the same as TIMESTAMP and CURRENT TIMESTAMP except that the date and time of day is in Coordinated Universal Time (UTC).
string See Strings.
global-variable See Global variables.
constant-expression Constant expressions that do not reference database objects are allowed in a DEFAULT clause, so functions such as GETDATE or DATEADD can be used. If the expression is not a function or simple value, it must be enclosed in parentheses.
sequence-expression You can set DEFAULT to the current value or next value from a sequence in the database.
AUTOINCREMENT When using AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type.
On inserts into the table, if a value is not specified for the AUTOINCREMENT column, a unique value larger than any other value in the column is generated. If an INSERT specifies a value for the column that is larger than the current maximum value for the column, that value is inserted and then used as a starting point for subsequent inserts.
Deleting rows does not decrement the AUTOINCREMENT counter. Gaps created by deleting rows can only be filled by explicit assignment when using an insert. After an explicit insert of a column value less than the maximum, subsequent rows without explicit assignment are still automatically incremented with a value of one greater than the previous maximum.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the next value to be generated exceeds the maximum value that can be stored in the column to which the AUTOINCREMENT is assigned, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated.
The next value to use for a column can be reset using the sa_reset_identity procedure.
GLOBAL AUTOINCREMENT This default is intended for use when multiple databases are used in a MobiLink synchronization environment or SQL Remote replication.
This option is similar to AUTOINCREMENT, except that the domain is partitioned. Each partition contains the same number of values. You assign each copy of the database a unique global database identification number. SQL Anywhere supplies default values in a database only from the partition uniquely identified by that database's number.
The partition size can be specified in parentheses immediately following the AUTOINCREMENT keyword. The partition size can be any positive integer, although the partition size is generally chosen so that the supply of numbers within any one partition will rarely, if ever, be exhausted.
If the column is of type BIGINT or UNSIGNED BIGINT, the default partition size is 232 = 4294967296; for columns of all other types, the default partition size is 216 = 65536. Since these defaults may be inappropriate, especially if your column is not of type INT or BIGINT, it is best to specify the partition size explicitly.
When using this default, the value of the public option global_database_id in each database must be set to a unique, non-negative integer. This value uniquely identifies the database and indicates from which partition default values are to be assigned. The range of allowed values is np + 1 to p(n + 1), where n is the value of the public option global_database_id and p is the partition size. For example, if you define the partition size to be 1000 and set global_database_id to 3, then the range is from 3001 to 4000.
If the previous value is less than p(n + 1), the next default value is one greater than the previous largest value in the column. If the column contains no values, the first default value is np + 1. Default column values are not affected by values in the column outside the current partition; that is, by numbers less than np + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink or SQL Remote.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
GLOBAL AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the supply of values within the partition has been exhausted, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated. In this case, a new value of global_database_id should be assigned to the database to allow default values to be chosen from another partition. To detect that the supply of unused values is low and handle this condition, create an event of type GlobalAutoincrement.
Because the public option global_database_id cannot be set to a negative value, the values chosen are always positive. The maximum identification number is restricted only by the column data type and the partition size.
If the public option global_database_id is set to the default value of 2147483647, a NULL value is inserted into the column. If NULL values are not permitted, attempting to insert the row causes an error.
The next value to use for a column can be reset using the sa_reset_identity procedure.
LAST USER LAST USER is the user ID of the user who last modified the row.
LAST USER can be used as a default value in columns with character data types.
On INSERT, this default has the same effect as CURRENT USER.
On UPDATE, if a column with a default of LAST USER is not explicitly modified, it is changed to the name of the current user.
When used in conjunction with DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP, a default of LAST USER can be used to record (in separate columns) both the user and the date and time a row was last changed.
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 This constraint allows arbitrary conditions to be verified. For example, a CHECK constraint could be used to ensure that a column called Sex only contains the values M or F.
If you need to create a CHECK constraint that involves a relationship between two or more columns in the table (for example, column A must be less than column B), define a table constraint instead.
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.
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.
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.
NULL and NOT NULL clauses Use this clause to specify whether to allow NULL values in the column. By default, NULLs are allowed.
INLINE and PREFIX clauses The INLINE clause specifies the maximum BLOB size, in bytes, to store within the row. BLOBs smaller than or equal to the value specified by the INLINE clause are stored within the row. BLOBs that exceed the value specified by the INLINE clause are stored outside the row in table extension pages. Also, a copy of some bytes from the beginning of the BLOB may be kept in the row when a BLOB is larger than the INLINE value. Use the PREFIX clause to specify how many bytes are kept in the row. The PREFIX clause can improve the performance of requests that need the prefix bytes of a BLOB to determine if a row is accepted or rejected.
The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary.
If neither INLINE nor PREFIX is specified, or if USE DEFAULT is specified, default values are applied as follows:
For character data type columns, such as CHAR, NCHAR, and LONG VARCHAR, the default value of INLINE is 256, and the default value of PREFIX is 8.
For binary data type columns, such as BINARY, LONG BINARY, VARBINARY, BIT, VARBIT, LONG VARBIT, BIT VARYING, and UUID, the default value of INLINE is 256, and the default value of PREFIX is 0.
It is strongly recommended that you use the default values unless there are specific circumstances that require a different setting. The default values have been chosen to balance performance and disk space requirements. For example, if you set INLINE to a large value, and all the BLOBs are stored inline, row processing performance may degrade. If you set PREFIX too high, you increase the amount of disk space required to store BLOBs since the prefix data is a duplicate of a portion of the BLOB.
If only one of the values is specified, the other value is automatically set to the largest amount that does not conflict with the specified value. Neither the INLINE nor PREFIX value can exceed the database page size. Also, there is a small amount of overhead reserved in a table page that cannot be used to store row data. Therefore, specifying an INLINE value approximate to the database page size can result in a slightly smaller number of bytes being stored inline.
INDEX and NO INDEX clauses When storing BLOBs (character or binary types only), specify INDEX to create BLOB indexes on inserted values that exceed the internal BLOB size threshold (approximately eight database pages). This is the default behavior.
BLOB indexes can improve performance when random access searches within the BLOBs are required. However, for some types of BLOB values, such as images and multimedia files that will never require random-access, performance can improve if BLOB indexing is turned off. To turn off BLOB indexing for a column, specify NO INDEX.
A BLOB index is not the same as a table index. A table index is created to index values in one or more columns.
IDENTITY clause IDENTITY is a Transact-SQL-compatible alternative to using DEFAULT AUTOINCREMENT. In SQL Anywhere, a column defined with IDENTITY is implemented as DEFAULT AUTOINCREMENT.
COMPUTE clause When a column is created using a COMPUTE clause, its value in any row is the value of the supplied expression. Columns created with this constraint are read-only columns for applications: the value is changed by the database server whenever the row is modified. The COMPUTE expression should not return a non-deterministic value. For example, it should not include a special value such as CURRENT TIMESTAMP, or a non-deterministic function. If a COMPUTE expression returns a non-deterministic value, then it cannot be used to match an expression in a query.
The COMPUTE clause is ignored for remote tables.
Any UPDATE statement that attempts to change the value of a computed column fires any triggers associated with the column.
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.
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.
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.
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.
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.
If you need to create a CHECK constraint that involves a relationship between two or more columns in the table (for example, column A must be less than column B), define a table constraint instead.
[ NOT ] COMPRESSED clause Use this clause to change whether the column is compressed.
INLINE and PREFIX clauses The INLINE clause specifies the maximum BLOB size, in bytes, to store within the row. BLOBs smaller than or equal to the value specified by the INLINE clause are stored within the row. BLOBs that exceed the value specified by the INLINE clause are stored outside the row in table extension pages. Also, a copy of some bytes from the beginning of the BLOB may be kept in the row when a BLOB is larger than the INLINE value. Use the PREFIX clause to specify how many bytes are kept in the row. The PREFIX clause can improve the performance of requests that need the prefix bytes of a BLOB to determine if a row is accepted or rejected.
The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary.
If neither INLINE nor PREFIX is specified, or if USE DEFAULT is specified, default values are applied as follows:
For character data type columns, such as CHAR, NCHAR, and LONG VARCHAR, the default value of INLINE is 256, and the default value of PREFIX is 8.
For binary data type columns, such as BINARY, LONG BINARY, VARBINARY, BIT, VARBIT, LONG VARBIT, BIT VARYING, and UUID, the default value of INLINE is 256, and the default value of PREFIX is 0.
It is strongly recommended that you use the default values unless there are specific circumstances that require a different setting. The default values have been chosen to balance performance and disk space requirements. For example, if you set INLINE to a large value, and all the BLOBs are stored inline, row processing performance may degrade. If you set PREFIX too high, you increase the amount of disk space required to store BLOBs since the prefix data is a duplicate of a portion of the BLOB.
If only one of the values is specified, the other value is automatically set to the largest amount that does not conflict with the specified value. Neither the INLINE nor PREFIX value can exceed the database page size. Also, there is a small amount of overhead reserved in a table page that cannot be used to store row data. Therefore, specifying an INLINE value approximate to the database page size can result in a slightly smaller number of bytes being stored inline.
INDEX and NO INDEX clauses When storing BLOBs (character or binary types only), specify INDEX to create BLOB indexes on inserted values that exceed the internal BLOB size threshold (approximately eight database pages). This is the default behavior.
BLOB indexes can improve performance when random access searches within the BLOBs are required. However, for some types of BLOB values, such as images and multimedia files that will never require random-access, performance can improve if BLOB indexing is turned off. To turn off BLOB indexing for a column, specify NO INDEX.
A BLOB index is not the same as a table index. A table index is created to index values in one or more columns.
SET COMPUTE clause When a column is created using a COMPUTE clause, its value in any row is the value of the supplied expression. Columns created with this constraint are read-only columns for applications: the value is changed by the database server whenever the row is modified. The COMPUTE expression should not return a non-deterministic value. For example, it should not include a special value such as CURRENT TIMESTAMP, or a non-deterministic function. If a COMPUTE expression returns a non-deterministic value, then it cannot be used to match an expression in a query.
The COMPUTE clause is ignored for remote tables.
Any UPDATE statement that attempts to change the value of a computed column fires any triggers associated with the column.
ALTER CONSTRAINT constraint-name CHECK clause Use this clause to alter a named check constraint for the table.
If you want to alter the constraint to specify a relationship between two or more columns in the table (for example, column A must be less than column B), you must define a table constraint instead.
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.
RENAME column-name TO new-column-name Change the name of the column to the new-column-name. Any applications using the old column name must be modified, as necessary.
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.
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.
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.
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.
RemarksThe 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.
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.
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.
PermissionsMust 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.
Side effectsAutomatic 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.
See also Standards and compatibilitySQL/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.
ExampleThe 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); |
The following example adds a column where the default is AUTOINCREMENT. In this example, all existing customer rows are modified to have a nullable AUTOINCREMENT column with the column value assigned, but the database server does not guarantee which row is assigned which value:
ALTER TABLE Customers ADD Surrogate_key INTEGER DEFAULT AUTOINCREMENT; |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |