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.
Use the sa_dependent_views system procedure to determine if there are dependent materialized views. See sa_dependent_views system procedure.
For more information about view dependencies, see View dependencies.
Connect to the database as a DBA user.
If you are performing a schema-altering operation, and there are dependent materialized views, disable them using the ALTER MATERIALIZED VIEW ... DISABLE statement for each dependent materialized view. You do not need to disable dependent non-materialized views.
Execute an ALTER TABLE statement to perform the table alteration.
The definition for the table in the database is updated.
If you disabled any materialized views, use the ALTER MATERIALIZED VIEW ... ENABLE statement to re-enable them.
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;