Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Working with database objects » Working with views » View dependencies


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)
 Dependencies and schema-altering changes (materialized views)