Alter tables (SQL)

You can alter tables in Interactive SQL using the ALTER TABLE statement. If you use a clause other than ADD FOREIGN KEY with the ALTER TABLE statement on a table with dependent materialized views, the ALTER TABLE statement fails. For all other clauses, you must disable the dependent materialized views and then re-enable them when your changes are complete. See View dependencies.

Use the sa_dependent_views system procedure to determine if there are dependent materialized views. See sa_dependent_views system procedure.

To alter an existing table (SQL)

  1. Connect to the database as a user with DBA authority.

  2. If you are performing a schema-altering operation on tables with dependent materialized views, and use a clause other than ADD FOREIGN KEY with the ALTER TABLE statement, use the ALTER MATERIALIZED VIEW ... DISABLE statement to disable each dependent materialized view. You do not need to disable dependent regular views.

  3. Execute an ALTER TABLE statement to perform the table alteration.

    The definition for the table in the database is updated.

  4. If you disabled any materialized views, use the ALTER MATERIALIZED VIEW ... ENABLE statement to re-enable them.

Examples

These examples show how to change the structure of the database. The ALTER TABLE statement can change just about anything pertaining to a table—you can use it to add or drop foreign keys, change columns from one type to another, and so on. In all these cases, once you make the change, stored procedures, views, and any other items referring to this table may no longer work.

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

ALTER TABLE Skills
ADD SkillDescription CHAR( 254 );

You can also alter column attributes with the ALTER TABLE statement. The following statement shortens the SkillDescription column from a maximum of 254 characters to a maximum of 80: 

ALTER TABLE Skills
ALTER SkillDescription CHAR( 80 );

By default, an error occurs if there are entries that are longer than 80 characters. The string_rtruncation option can be used to change this behavior. See string_rtruncation option [compatibility].

The following statement changes the name of the SkillType column to Classification:

ALTER TABLE Skills
RENAME SkillType TO Classification;

The following statement drops the Classification column.

ALTER TABLE Skills
DROP Classification;

The following statement changes the name of the entire table:

ALTER TABLE Skills
RENAME Qualification;
See also
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1