db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dag.wan...@oracle.com (Dag H. Wanvik)
Subject Re: Views dropped automatically when altering tables?
Date Tue, 07 Feb 2012 16:38:40 GMT
John English <john.foreign@gmail.com> writes:

> Hi all,
> When altering a table the other day I discovered that any views that
> reference the table get dropped automatically (and silently), as do
> any views that depend on those views, and so on. This came as a nasty
> surprise to me when I tried to access one of those views!
>
> If the table has associated triggers I get an error when I try to alter
> it, so I know which triggers are involved and I can then drop them and
> re-create them after the alteration. Shouldn't the same be true for
> views to avoid surprises? Or does the standard mandate this bizarre
> behaviour somewhere?

How did you alter the table?

At least for the DROP COLUMN feature, the syntax looks like this:

DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]

The CASCADE gives the behavior you see, the RESTRICT should balk.

In the documentation, we see that CASCADE is the default:

http://db.apache.org/derby/docs/10.8/ref/rrefsqlj81859.html

"The keywords CASCADE and RESTRICT are also optional. If you specify
neither CASCADE nor RESTRICT, the default is CASCADE."

The SQL standard makes the <drop behavior> specification mandatory (no
default), see ISO/IEC 9075-2:2003 (E) section 11.18 <drop column
definition>.

Thanks,
Dag


>
> I'd be glad to hear any informed opinions on this...
>
> ------------------------------------------------------------------------
>  John English | My old University of Brighton home page is still here:
>               | http://www.cem.brighton.ac.uk/staff/je/
> ------------------------------------------------------------------------

Mime
View raw message