ASA SQL Reference
  SQL Statements

ALTER TABLE statement

Description 

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

Syntax 1 

ALTER TABLE [ owner.]table-name
{ add-clause | modify-clause | drop-clause | rename-clause }

add-clause :
   ADD { column-definition | table-constraint }
| { ADD PCTFREE integer | PCTFREE DEFAULT }

modify-clause :
  MODIFY column-definition
| MODIFY column-name { DEFAULT default-value
   | [ NOT ] NULL
   | [ CONSTRAINT constraint-name ]
         CHECK { NULL |  ( new-condition ) } }
| ALTER column-name  column-modification
| ALTER constraint-name CHECK ( new-condition )

drop-clause :
{ DELETE | DROP }{
    column-name
   | CONSTRAINT constraint-name
   | CHECK
   | UNIQUE ( column-name, ... )
   | PRIMARY KEY
   | FOREIGN KEY role-name }

rename-clause :
  RENAME  new-table-name
| RENAME column-name TO new-column-name
| RENAME constraint-name TO new-constraint-name

column-definition :
column-name data-type [ NOT NULL ] [ DEFAULT default-value ] [ column-constraint ... ]

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

column-constraint :
[ CONSTRAINT constraint-name ] {  UNIQUE
  | PRIMARY KEY
  | REFERENCES table-name
      [ ( column-name ) ] [ actions ] [ CLUSTERED ]
  | CHECK ( condition ) }
| COMPUTE ( expression )

column-modification :
  SET DEFAULT default-value
| DROP DEFAULT
| ADD [ CONSTRAINT column-constraint-name ] CHECK ( condition )
| { DELETE | DROP } CONSTRAINT column-constraint-name
| { DELETE | DROP } CHECK
| SET COMPUTE ( expression )
| DROP COMPUTE

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-constraint :
[ NOT NULL ] FOREIGN KEY [ role-name ] [ (column-name, ... ) ]
REFERENCES table-name [ (column-name, ... ) ] [ CLUSTERED ]
[ actions ] [ CHECK ON COMMIT ]

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

action :
CASCADE | SET NULL | SET DEFAULT | RESTRICT

Syntax 2 

ALTER TABLE [ owner.]table-name REPLICATE { ON | OFF }

Parameters 

add-clause    Add a new column or table constraint to the table. For more information, see below.

modify-clause    Change a single column definition. For more information, see below.

drop-clause    Drop a column or a table constraint. For more information, see below.

rename-clause    Change the name of the table, a column, or a constraint. For more information, see below.

ADD column-definition    Add a new column to the table.

If the column has a default value, all rows of the new column are populated with that default value.

Adaptive Server Anywhere optimizes the creation of columns that are allowed to contain NULL. The first column allowed to contain NULL allocates room for eight such columns, and initializes all eight to be NULL. (This requires no extra storage.) Thus, the next seven columns added require no changes to the rows of the table. Adding a ninth column then allocates room for another eight such columns and modifies each row of the table to allocate the extra space.

ADD table-constraint    Add a constraint to the table. 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 that was created by the CREATE TABLE statement or another ALTER TABLE statement.

The optional constraint name allows you to modify or drop individual constraints at a later time, rather than having to modify the entire table constraint.

Table CHECK constraints fail only if a value of FALSE is returned. A value of UNKNOWN allows the change.

PCTFREE    Specify the percentage of free space you want to reserve for 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 if DEFAULT is specified, 200 bytes are reserved in each page.

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 or the table is dropped.

The PCTFREE specification can be used for base, global temporary, or local temporary tables. Except for local temporary tables, the value for PCTFREE is stored in the SYSATTRIBUTE system table.

For more information, see SYSATTRIBUTE system table.

MODIFY column-definition    Change the length or data type of an existing column in a table. If NOT NULL is specified, a NOT NULL constraint is added to the named column. Otherwise, the NOT NULL constraint for the column will not be changed. If necessary, the data in the modified column will be converted to the new data type. If a conversion error occurs, the operation will fail and the table will be left unchanged.

You cannot modify a column to make it a computed column. Computed columns can only be added or dropped.

MODIFY column-name DEFAULT default-value    Change the default value of an existing column in a table. To remove a default value for a column, specify DEFAULT NULL. Modifying a default value does not change any existing values in the table.

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 SQL/92 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 SQL/92 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 allows you to 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 non-computed column. This statement does not change any existing values in the table.

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 CHECK NULL    Delete the check constraint for the column. This statement cannot be used on databases created before version 5.0.

MODIFY column-name CHECK (condition)    Replace the existing CHECK condition for the column with the one specified. This statement cannot be used on databases created before version 5.0.

DELETE column-name    Delete the column from the table. If the column is contained in any 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.

DELETE constraint-name    Delete the named constraint from the table definition.

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.

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.

DELETE FOREIGN KEY role-name    Delete the foreign key constraint for this table with the given role name.

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. Foreign keys that were automatically assigned 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.

Usage 

Syntax 1    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. A table cannot be both added and modified in the same 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 server will not process requests referencing the table while the statement is being processed.

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

Before version 5.0, all table and column constraints were held in a single table constraint. Consequently, for these databases individual constraints on columns cannot be deleted using the MODIFY column-name CHECK NULL clause or replaced using the MODIFY column-name CHECK (condition ) clause. To use these statements, the entire table constraint should be deleted and the constraints added back using the MODIFY column-name CHECK (condition ) clause. At this point you can use MODIFY CHECK.

Syntax 2    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. The remainder of this section describes syntax 1.

Permissions 

Must be one of the following:

• The owner of the table.

• A user with DBA authority.

• A user 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.

Side effects 

Automatic commit.

The MODIFY and DELETE (DROP) options close all cursors for the current connection.

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.

See also 

CREATE TABLE statement

DROP statement

SQL Data Types

Altering tables

Special values

Using table and column constraints

Standards and compatibility 

• SQL/92    Intermediate-level feature. MODIFY is not SQL/92 compliant.

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

• Sybase    Some clauses are supported by Adaptive Server Enterprise.

Example 

The following example adds a new column to the employee table showing which office they work in.

ALTER TABLE employee
ADD office CHAR(20) DEFAULT 'Boston'

The following example drops the office column from the employee table.

ALTER TABLE employee
DELETE office

The address column in the customer table can currently hold up to 35 characters. To allow it to hold up to 50 character, type the following.

ALTER TABLE customer
MODIFY address CHAR(50)

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

ALTER TABLE customer
ADD sales_contact INTEGER
REFERENCES employee (emp_id)
ON UPDATE CASCADE
ON DELETE SET NULL

This foreign key is constructed with cascading updates and is set 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.