Hi Bryan,
DROP COLUMN will also need to see if there are any privileges granted on it and if yes, then those privileges should be revoked. There might be views/triggers/constraints dependent on that privilege but I think in the case of RESTRICT, the DROP COLUMN will fail anyways because of the dependent objects.
Are we implementing DROP COLUMN cascade too?
I haven't given this a whole lot of thought but wanted to bring up the privilege issue with the DROP COLUMN functionality.
thanks,
Mamta
On 7/10/06, Rick Hillegas <[EMAIL PROTECTED]> wrote:
Hi Bryan,
My $0.02 follows. Cheers-Rick
Bryan Pendleton wrote:
> ...
>
> a) RESTRICT processing should consider an index on a column to be
> a dependent object and fail the DROP COLUMN if the column is used
> in an index?
The ANSI spec doesn't provide much guidance here since ANSI doesn't talk
about indexes. I would recommend using DROP TABLE as your guide.
Dropping a table implicitly drops all indexes defined on that table. I
would expect that dropping a column would implictly drop all indexes
which mention that column.
>
> b) CASCADE processing should cascade the DROP COLUMN to include
> dropping a view which uses the column that is dropped?
My reading of the ANSI spec is that CASCADE is the opposite of RESTRICT.
That is, if you specify CASCADE, then the database will drop the
dependent objects which blocked the RESTRICTed version of the statement.
According to the ANSI spec (Volume 2, section 11.18), these dependent
objects include:
o views which mention the column
o certain constraints
o triggers which mention the column
o generated columns whose definitions mention the dropped column (we
don't support this yet)
o sql routines which mention the column (again, we don't support this)
So my reading of the ANSI spec suggests that CASCADE should drop views
which mention the column.
I think that the RESTRICT-blocking logic is smart enough to compute the
closure of dropped objects (the dependents of the dependents and so on).
I would think that CASCADE would drop everything in that closure.
>
> thanks,
>
> bryan
>
