Re: Why can we not delete columns in tables?

Martin Haltmayer wrote:

> I can imagine a few good reasons:
> 1. It is not allowed in ANSI-SQL.

ANSI SQL specifically provides for this feature. I refer you to Date's 'Guide to the SQL Standard'

> 2. If you drop a column, you have to invalidate all the checks,
> constraints, indexes, triggers, views, procedures and functions that use
> that column. That's a lot more work to do than just dropping these
> objects when a table is dropped.

A properly constructed schema would permit a 'where used' query. All dependant objects would have to deleted first. A 'cascade delete' could also be provided.

> 3. As another Oraclist mentioned, it would be a very time consuming
> procedure that may lock the data dictionary unintentionally for a very
> long time. You may just estimate the amount of time doing an update on
> that column to null.

Don't know what 'unintentionally' means in this sentence, or for that matter a 'very long time'.

I asked Oracle why they do no provide this capability, which to me is integral to the whole SQL model. I initially got an answer along the lines of 'to ensure data integrity'. When I knocked down this argument, they dried up.

I don't know why they do not provide this facility. My suspicion it will break their applications, Forms, and probably their DBMS big time.

Does any one know if they provided ALTER TABLE DROP COLUMN in previous versions.

> Phil B.
> --
> Martin Haltmayer
Received on Mon Oct 06 1997 - 00:00:00 CDT