Modifies a table definition.
ALTER TABLE [ owner.]table-name
{ add-clause | modify-clause | drop-clause | rename-clause }
ADD column-definition [ column-constraint ]... | ADD table-constraint
MODIFY column-definition | MODIFY column-name [ IDENTITY | DEFAULT default-value ] [ NOT ] NULL | MODIFY column-name [ CONSTRAINT constraint-name ] CHECK NULL | MODIFY column-name CHECK ( new-condition ) ALTER column-name column-modification | ALTER CONSTRAINT constraint-name CHECK (new-condition )
{ DELETE | DROP } column-name | { DELETE | DROP } CHECK | { DELETE | DROP } CONSTRAINT constraint-name | { DELETE | DROP } UNIQUE ( column-name [, ...] ) | { DELETE | DROP } PRIMARY KEY | { DELETE | DROP } FOREIGN KEY role-name
RENAME new-table-name | RENAME column-name TO new-column-name | RENAME constraint-name TO new-constraint-name
column-name data-type [ NOT NULL ] [ DEFAULT default-value | IDENTITY ]
[ CONSTRAINT constraint-name ] { UNIQUE | PRIMARY KEY | REFERENCES table-name [ ( column-name ) ] [ actions ] | CHECK ( condition ) | IQ UNIQUE ( integer ) }
special-value | string | global variable | [ - ] number | ( constant-expression ) | built-in-function ( constant-expression ) | AUTOINCREMENT | NULL | TIMESTAMP | LAST USER | USER
CURRENT { DATABASE | DATE | REMOTE USER | TIME | TIMESTAMP | USER | PUBLISHER }
{ UNIQUE ( column-name [, ...] ) | PRIMARY KEY ( column-name [, ...] ) | foreign-key-constraint | CHECK ( condition )}
FOREIGN KEY [ role-name ] [ (column-name [, ...] ) ] ... REFERENCES table-name [ (column-name [, ...] ) ] ... [ actions ] [
[ ON {UPDATE | DELETE} action ]
{ RESTRICT }
Adds a new column to the employees table showing which office they work in:
ALTER TABLE employee ADD office CHAR(20)
Drops the office column from the employees table:
ALTER TABLE employee DELETE office
Adds a column to the customer table assigning each customer a sales contact:
ALTER TABLE customer ADD sales_contact INTEGER REFERENCES employee (emp_id)
Adds a new column cust_num to the customer table and assigns a default value of 88:
ALTER TABLE customer ADD cust_num INTEGER DEFAULT 88
The ALTER TABLE statement changes table attributes (column definitions and constraints) in a table that was previously created. The syntax allows a list of alter clauses; however, only one table constraint or column constraint can be added, modified, or deleted in each ALTER TABLE statement.
You cannot alter local temporary tables, but you can
alter global temporary tables when they are in use by only one connection.
Sybase IQ enforces REFERENCES and CHECK constraints. Table and/or column check constraints added in an ALTER TABLE statement are not evaluated as part of that alter table operation. For details about CHECK constraints, see CREATE TABLE statement.
If SELECT * is used in a view definition and you alter a table referenced by the SELECT *, then you must run ALTER VIEW <viewname> RECOMPILE to ensure that the view definition is correct and to prevent unexpected results when querying the view.
ADD column-definition [ column-constraint ] Add a new column to the table. The table must be empty to specify NOT NULL. The table might contain data when you add an IDENTITY or DEFAULT AUTOINCREMENT column. If the column has a default IDENTITY value, all rows of the new column are populated with sequential values. You can also add a foreign key constraint as a column constraint for a single column key. The value of the IDENTITY/DEFAULT AUTOINCREMENT column uniquely identifies every row in a table. The IDENTITY/DEFAULT AUTOINCREMENT column stores sequential numbers that are automatically generated during inserts and updates. DEFAULT AUTOINCREMENT columns are also known as IDENTITY columns. When using IDENTITY/DEFAULT AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type, with scale 0. See CREATE TABLE statement for more about column constraints and IDENTITY/DEFAULT AUTOINCREMENT columns.
You cannot add foreign key constraints to an unenforced
primary key created with Sybase IQ version 12.4.3 or earlier.
ADD table-constraint Add a constraint to the table. You can also add a foreign key constraint as a table constraint for a single-column or multicolumn key. See CREATE TABLE statement for a full explanation of table constraints.
If PRIMARY KEY is specified, the table must not already have a primary key created by the CREATE TABLE statement or another ALTER TABLE statement.
You cannot MODIFY a table or column
constraint. To change a constraint, DELETE the
old constraint and ADD the new constraint.
MODIFY column-name [ NOT ] NULL Change the NOT NULL constraint on the column to allow or disallow NULL values in the column.
MODIFY column-name [ DEFAULT default-value | IDENTITY ] The value of the IDENTITY or DEFAULT AUTOINCREMENT column uniquely identifies every row in a table. The IDENTITY/DEFAULT AUTOINCREMENT column stores sequential numbers that are automatically generated during inserts and updates. DEFAULT AUTOINCREMENT columns are also known as IDENTITY columns. When you use IDENTITY/DEFAULT AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type, with scale 0. See CREATE TABLE statement for a full explanation of column constraints and IDENTITY/DEFAULT AUTOINCREMENT columns.
ALTER TABLE also supports the modification of column default values other than IDENTITY/DEFAULT AUTOINCREMENT. When modifying a column of a table, you can specify a default value for the column using the DEFAULT keyword. If a DEFAULT value is specified for a column, this DEFAULT value is used as the value of the column in any INSERT (or LOAD) statement that does not specify a value for the column.
For detailed information on the use of column DEFAULT values, see “Using column defaults” in Chapter 9, “Ensuring Data Integrity” in the Sybase IQ System Administration Guide.
MODIFY column-name CHECK NULL Delete the check constraint for the column.
MODIFY column-name CHECK (new-condition) Replace the existing CHECK condition for the column with the one specified.
ALTER column-name column-modification Change the definition of a column. The permitted modifications are as follows:
SET DEFAULT default-value Change the default value of an existing column in a table. You can also use the MODIFY clause for this task, but ALTER is SQL92 compliant, and MODIFY is not. Modifying a default value does not change any existing values in the table.
DROP DEFAULT Remove the default value of an existing column in a table. You can also use the MODIFY clause for this task, but ALTER is SQL92 compliant, and MODIFY is not. Dropping a default does not change any existing values in the table.
ADD Add a named constraint or a CHECK condition to the column. The new constraint or condition applies only to operations on the table after its definition. The existing values in the table are not validated to confirm that they satisfy the new constraint or condition.
CONSTRAINT column-constraint-name The optional column constraint name lets you modify or drop individual constraints at a later time, rather than having to modify the entire column constraint.
SET COMPUTE (expression) Change the expression associated with a computed column. The values in the column are recalculated when the statement is executed, and the statement fails if the new expression is invalid.
DROP COMPUTE Change a column from being a computed column to being a noncomputed column. This statement does not change any existing values in the table.
DELETE column-name Delete the column from the table. If the column is contained in any multicolumn index, uniqueness 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. An IDENTITY/DEFAULT AUTOINCREMENT column can only be deleted if IDENTITY_INSERT is turned off and the table is not a local temporary table.
DELETE CHECK Delete all check constraints for the table. This includes both table check constraints and column check constraints.
DELETE UNIQUE (column-name,...) Delete a uniqueness constraint for this table. Any foreign keys referencing this uniqueness constraint (rather than the primary key) are also deleted. Reports an error if there are associated foreign-key constraints. Use ALTER TABLE to delete all foreign keys that reference the primary key before you delete the primary key constraint.
DELETE PRIMARY KEY Delete the primary key constraint for this table. All foreign keys referencing the primary key for this table are also deleted. Reports an error if there are associated foreign key constraints. If the primary key is unenforced, DELETE returns an error if associated unenforced foreign key constraints exist.
DELETE FOREIGN KEY role-name Delete the foreign key constraint for this table with the given role name. Retains the implicitly created nonunique HG index for the foreign key constraint. Users can explicitly remove the HG index with the DROP INDEX statement.
RENAME new-table-name Change the name of the table to the new-table-name. Any applications using the old table name must be modified. Also, any foreign keys that were automatically assigned the same name as the old table name do not change names.
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.
RENAME constraint-name TO new-constraint-name Change the name of the constraint to the new-constraint-name. Any applications using the old constraint name must be modified.
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 server does not process requests referencing the same table while the statement is being processed.
Automatic commit. The MODIFY and DELETE options close all cursors for the current connection. The DBISQL data window is also cleared.
A checkpoint is carried out at the beginning of the ALTER TABLE operation.
Once you alter a column or table, any stored procedures, views or other items that refer to the altered column no longer work.
SQL92 Intermediate-level feature. MODIFY clauses are not SQL92 compliant.
Sybase Some clauses are supported by Adaptive Server Enterprise.
Must be the owner of the table or have DBA authority or ALTER permission on the table. Requires exclusive access to the table.
