Altering tables

This section describes how to change the structure of a table using the ALTER TABLE statement.

Example 1

The following command adds a column to the skill table to allow space for an optional description of the skill:

ALTER TABLE skill
ADD skill_description CHAR( 254 )

Example 2

The following statement changes the name of the skill_type column to classification:

ALTER TABLE skill
RENAME skill_type TO classification

Example 3

The following statement deletes the classification column.

ALTER TABLE skill
DELETE classification

Example 4

The following statement changes the name of the entire table:

ALTER TABLE skill
RENAME qualification

These examples show how to change the structure of the database. The ALTER TABLE statement can change many characteristics of a table—foreign keys can be added or deleted, and so on. However, you cannot use MODIFY to change table or column constraints. Instead, you must DELETE the old constraint and ADD the new one. In all these cases, once you make the change, stored procedures, views, and any other item referring to this column will no longer work.

For a complete description, see ALTER TABLE statement in Reference: Statements and Options. For information about building constraints into table definitions using ALTER TABLE, see Chapter 9, “Ensuring Data Integrity”

Altering tables in Sybase Central

To alter a table definition in Sybase Central, see the Sybase Central online help.

Altering tables in a join index

You cannot ADD, DROP or MODIFY a base table column that participates in a join condition of a join index. To alter joined columns, you must first drop the join index, alter the table, and then recreate the join index. See “Using join indexes” for complete information on join indexes.