ALTER TABLE statement

Reference Manual
CHAPTER 9. SQL Statements

ALTER TABLE statement

Function

To modify a table definition.

Also, to enable a table to take part in a Replication Server replication.

Syntax 1

ALTER TABLE [ owner.]table-name
...
ADD column-definition [column-constraint ...]
| ADD table-constraint
| MODIFY column-definition
| MODIFY column-name DEFAULT default-value
| MODIFY column-name [ NOT ] NULL
| MODIFY column-name CHECK NULL
| MODIFY column-name CHECK ( condition )
| { DELETE | DROP } column-name
| { DELETE | DROP } CHECK
| { 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

Syntax 2

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

Parameters

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

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

table-constraint:
UNIQUE ( column-name, ... )
| PRIMARY KEY ( column-name, ... )
| CHECK ( condition )
| foreign-key-constraint

foreign-key-constraint:
[ NOT NULL ] FOREIGN KEY [ role-name ] [ (column-name, ... ) ]
... REFERENCES table-name [ (column-name, ... ) ]
... [ actions ] [ CHECK ON COMMIT ]

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

action:
CASCADE
| SET NULL
| SET DEFAULT
| RESTRICT

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.

Description

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.

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.

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.

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.

ADD column-definition Add a new column to the table. The table must be empty to specify NOT NULL.

NULL values
Adaptive Server Anywhere optimizes the creation of columns which 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. Consequently, seven out of eight column additions run quickly.

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.

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.

Deleting an index, constraint, or key
If the column is contained in a uniqueness constraint, a foreign key, or a primary key, then the constraint or key must be deleted before the column can be modified. If a primary key is deleted, all foreign keys referencing the table will also be deleted.

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

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

Standards and compatibility

SQL/92 Intermediate level feature.
Sybase Supported by Adaptive Server Enterprise.

Examples

Add a new column to the employees table showing which office they work in.
```
ALTER TABLE employee
ADD office CHAR(20) DEFAULT 'Boston'
```
Drop the office column from the employees table.
```
ALTER TABLE employee
DELETE office
```
The address column in the customer table can currently hold up to 35 characters. Allow it to hold up to 50 characters.
```
ALTER TABLE customer
MODIFY address CHAR(50)
```
Add 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 a 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.

ALTER TABLE statement

Function

Syntax 1

Syntax 2

Parameters

Permissions

Side effects

See also

Description

Standards and compatibility

Examples