Modifies a table definition.
ALTER TABLE [ owner.]table-name
{ ADD column-definition [ column-constraint ]...
| ADD table-constraint
| MODIFY column-definition
| MODIFY column-name[ IDENTITY ] [ DEFAULT AUTOINCREMENT ] [ NOT ] NULL
| MODIFY column-name [ CONSTRAINT constraint-name ] CHECK NULL
| MODIFY column-name CHECK ( new-condition )
| ALTER column-name column-modification
| ALTER 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 ] [ IDENTITY ] [ DEFAULT AUTOINCREMENT ]
[ CONSTRAINT constraint-name ] { UNIQUE | PRIMARY KEY | REFERENCES table-name [ ( column-name ) ] [ actions ] | CHECK ( condition ) | IQ UNIQUE ( integer ) }
{ 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 }
Add a new column to the employees table showing which office they work in.
ALTER TABLE employee ADD office CHAR(20)
Drop the office column from the employees table.
ALTER TABLE employee DELETE office
Add a column to the customer table assigning each customer a sales contact.
ALTER TABLE customer ADD sales_contact INTEGER REFERENCES employee (emp_id)
The ALTER TABLE statement changes table attributes (column definitions, constraints) in a table that was previously created. Note that the syntax allows a list of alter clauses; however, only one table-constraint or column-constraint can be added, modified or deleted in one 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 may 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. 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 12.4.3 or earlier versions.
ADD table-constraint Add a constraint to the table. Can also add a foreign key constraint as a table constraint for a single 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, you must 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 [ IDENTITY ] [ DEFAULT AUTOINCREMENT ] 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 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 a full explanation of column constraints and IDENTITY/DEFAULT AUTOINCREMENT columns.
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.
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 set OFF for the table 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) will also be deleted. Reports an error if there are associated foreign key constraints. You must use ALTER TABLE to delete all foreign keys that reference the primary key before you can 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 will also be 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 non-unique 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. Note that any applications using the old table name will need to be modified. Also, any foreign keys which were automatically assigned the same name as the old table name will not change names.
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.
RENAME constraint-name TO new-constraint-name Change the name of the constraint to the new-constraint-name. Note that any applications using the old constraint name will need to be modified.
ALTER TABLE will be prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time consuming and the server will 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.
SQL/92 Intermediate level feature. MODIFY clauses are not SQL/92 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.
