You can alter tables in Interactive SQL using the ALTER TABLE statement. Altering tables fails if there are any dependent materialized views; you must first disable dependent materialized views. Once your table alterations are complete, you must re-enable the dependent materialized views. See View dependencies.

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

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

• ALTER TABLE statement
• ALTER VIEW statement
• Altering regular views
• Enabling and disabling materialized views
• ALTER MATERIALIZED VIEW statement
• Ensuring data integrity
• View dependencies