Understanding the CASCADE and RESTRICT Clauses in a DROP VIEW Statement

As mentioned in Tip 64, the DBMS will not erase any database data when you drop a view. However, you still have to be careful that the view you are dropping is not referenced by another view. Some DBMS products let you add the CASCADE or RESTRICT clause to a DROP VIEW statement to control the behavior of the DBMS when you tell the system to DROP a view that is referenced by another view.

If you execute the DROP VIEW statement with the CASCADE clause, the DBMS will not only remove the view you name in the DROP VIEW statement, but also any other view that references the view in the DROP VIEW statement. For example, if you have two views as defined by

CREATE VIEW vw_sales_production AS

SELECT rep_id, calls, sales, deliveries FROM production

and

CREATE VIEW vw_delivered_sales_commissions

(rep_id, deliveries, commission) AS

SELECT rep_id, deliveries, deliveries * 150.00

FROM vw_sales_production

when you execute

DROP VIEW vw_sales_production

the DBMS will remove only the VW_SALES_PRODUCTION view from the system tables. If you execute the SELECT statement

SELECT * FROM vw_delivered_sales_commissions

after you DROP the VW_SALES_PRODUCTION view, the DBMS will respond with an error message in the form:

Server: Msg 208, Level 16, State 1,

Procedure vw_delivered_sales_commissions, Line 1

Invalid object name 'vw_sales_production'.

Server: Msg 4413, Level 16, State 1, Line 1

Could not use view ' vw_delivered_sales_commissions'

previous binding errors.

If, on the other hand, you DROP the VW_SALES_PRODUCTION view with the DROP VIEW statement

DROP VIEW vw_sales_production CASCADE

the DBMS will remove both VW_SALES_PRODUCTION and VW_DELIVERED_SALES_COMMISSIONS (which references it) from the system tables.

Conversely, some DBMS products allow you to add the RESTRICT clause to the DROP VIEW statement. The RESTRICT clause will prevent you from dropping a view that is referenced by another view Thus, in the current example, executing the "restricted" DROP VIEW statement

DROP VIEW vw_sales_production RESTRICT

will fail because the view VW_SALES_PRODUCTION view is referenced by the VW_DELIVERED_SALES_COMMISSIONS view.

Note Not all DBMS products provide the CASCADE and RESTRICT clauses for the DROP VIEW statement-MS-SQL Server, for example, does not. As such, check your system documentation to see if you can add the CASCADE or RESTRICT clause to the DROP VIEW statement in your