Re: Should this require CASCADE?

On Wed, 10 Jul 2002, Tom Lane wrote:

> CREATE TABLE foo (f1 int primary key);
> CREATE TABLE bar (f1 int references foo);
> DROP TABLE foo RESTRICT;
>
> Should this succeed? Or should it be necessary to say DROP CASCADE to
> get rid of the foreign-key reference to foo?
>
> Our historical behavior is to allow the drop, while issuing a notice
> about implicit deletion of triggers. But I think SQL92 intends that
> CASCADE should be required.

I don't think it should succeed, no; to me the historical behaviour
seems wrong. To get a bit Dateish (or is that Datish?) for a moment,
when you created table bar, you added this rule to the set of rules
for your database:

For every f1 in bar, there exists the same f1 in foo.

If you allow table foo to be dropped, you make that statement false. But
I think removing that rule should be an explicit, not implicit action.

And as far as the compatability thing goes, well, probably pretty
much everyone agrees that it's better to break things such that
you are less likely to lose data....

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC