Drop behavior in SQL DROP VIEW statement

Question

Is the Statement:

DROP VIEW <view-name>

a valid sql92 syntax? In some Databases (e.g Postgres, Redshift) there is the option to explicitly define a drop behavior

DROP VIEW <view-name> [RESTRICT|CASCADE]

While for others it is an obligatory (e.g Netteza)

DROP VIEW <view-name> {RESTRICT|CASCADE}

or simply unsupported in the syntax (e.g Transact-sql).

What does the sql92 standard state about drop view syntax regarding drop behavior? I didn't find any mention to drop behavior in the sql92 syntax.

score 1 · Answer 1 · answered Jul 11 '17 at 11:17

You can check wiki article . At the bottom, in the "External Links" you can find The SQL-92 standard. Scroll down to the "X3H2-92-154/DBL CBR-002, 11.20 " and there you can find:

     <drop view statement> ::=
          DROP VIEW <table name> <drop behavior>

        Note: If CASCADE is specified, then any such dependent object
        will be dropped by the execution of the <revoke statement> spec-
        ified in the General Rules of this Subclause.

score 1 · Accepted Answer · answered Jul 11 '17 at 11:17

Both RESTRICT and CASCADE are mentioned in the ANSI-92 standard as <drop behavior> qualifiers in section 11.20

If RESTRICT is specified, then V shall not be referenced in the of any view descriptor or the of any assertion descriptor or constraint descriptor

Note: If CASCADE is specified, then any such dependent object will be dropped by the execution of the spec- ified in the General Rules of this Subclause.

Search <drop behavior> in here, 6th occurence for views
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Rahul · Answer 3 · 2017-07-11T11:17:21.557

Yes there is per SQL 92 standard and the syntax is

<drop view statement> ::=
              DROP VIEW <table name> <drop behavior>

Syntax Rules

1) Let V be the table identified by the and let VN be that . The schema identified by the explicit or implicit schema name of VN shall include the descriptor of V.

2) V shall be a viewed table.

3) If RESTRICT is specified, then V shall not be referenced in the of any view descriptor or the of any assertion descriptor or constraint descriptor.
Note: If CASCADE is specified, then any such dependent object
will be dropped by the execution of the <revoke statement> spec-
ified in the General Rules of this Subclause.
Access Rules

1) The current shall be equal to the that owns the schema identified by the of the table identified by VN.

General Rules

1) Let A be the current . The following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking:
  REVOKE ALL PRIVILEGES ON VN FROM A CASCADE
2) The identified view and its descriptor are destroyed.

Leveling Rules

1) The following restrictions apply for Intermediate SQL:
  None.
2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:
a) Conforming Entry SQL language shall not contain a <drop view
  statement>.

I've mistakenly written CREATE instead of DROP. You may want to update your answer — Daniel Heilper, Jul 11 '17 at 11:00
@gbn, NO, its an copy/paste from the direct SQL92 reference document and been posted before you actually answered. am in no need to copy other answer ... by the time modified my original answer and posted this you commented — Rahul, Jul 11 '17 at 11:43

Drop behavior in SQL DROP VIEW statement

3 Answers3