Dependencies and schema-altering changes

An attempt to alter the schema defined for a table or view requires that the database server consider if there are dependent views impacted by the change. Examples of schema-altering operations include:

  • Dropping a table, view, materialized view, or column

  • Renaming a table, view, materialized view, or column

  • Adding, dropping, or altering columns

  • Altering a column's data type, size, or nullability

  • Disabling views or table view dependencies

When you attempt a schema-altering operation, the following events occur:

  1. The database server generates a list of views that depend directly or indirectly upon the table or view being altered. Views with a DISABLED status are ignored.

    If any of the dependent views are materialized views, the request fails, an error is returned, and the remaining events do not occur. You must explicitly disable dependent materialized views before you can proceed with the schema-altering operation. See Enable and disable materialized views.

  2. The database server obtains exclusive schema locks on the object being altered, and on all dependent regular views.

  3. The database server sets the status of all dependent regular views to INVALID.

  4. The database server performs the schema-altering operation. If the operation fails, the locks are released, the status of dependent regular views is reset to VALID, an error is returned, and the following step does not occur.

  5. The database server recompiles the dependent regular views, setting each view's status to VALID when successful. If compilation fails for any regular view, the status of that view remains INVALID. Subsequent requests for an INVALID regular view causes the database server to attempt to recompile the view. If subsequent attempts fail, it is likely that an alteration is required on the INVALID view, or on an object upon which it depends.

Dependencies and schema-altering changes (regular views)

  • A regular view can reference tables or views, including materialized views.

  • When you change the schema of a table or view, the database automatically attempts to recompile all referencing regular views.

  • When you disable or drop a view or table, all dependent regular views are automatically disabled.

  • You can use the DISABLE VIEW DEPENDENCIES clause of the ALTER TABLE statement to disable dependent regular views.

Dependencies and schema-altering changes (materialized views)

  • A materialized view can only reference base tables.

  • Schema changes to a base table is not permitted if it is referenced by any enabled materialized views. You can add foreign keys to the table, however (for example, ALTER TABLE ADD FOREIGN KEY).

  • Before you drop a table, you must disable or drop all dependent materialized views.

  • The DISABLE VIEW DEPENDENCIES clause of the ALTER TABLE statement does not impact materialized views. To disable a materialized view, you must use the ALTER MATERIALIZED VIEW ... DISABLE statement.

  • Once you disable a materialized view, you must explicitly re-enable it, for example using the ALTER MATERIALIZED VIEW ... ENABLE statement.

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1