Re: Should this require CASCADE?

On Wed, 10 Jul 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> 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?
>
> > I think the above should fail. If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
>
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now. What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE. I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.
>
> So if you prefer, imagine same example but you merely say
> DROP TABLE foo;
> Does your answer change?

That's tougher. If I had a choice without worrying about the complexities
involved, I'd say that DROP TABLE foo; should restrict unless the only
references were from foreign keys and that those should cascade which is
the similar behavior to past versions without the really unsafe
referencing things that don't exist, and restrict and cascade should work
as specified. However, that adds effectively a third drop behavior and
one that isn't in the spec and would have to be documented, however I
think (unless I misread the spec) it wouldn't directly conflict with the
spec since drop behavior isn't optional.

Given that that's a can of worms we probably don't want to open, I
think restrict is probably safer behavior even though it breaks
compatibility with old versions even more than the above, but I think
silently cascading will be more difficult for users (hey, where did
my definition of <X> go?).