Re: Should this require CASCADE?

I think that we are getting into two or three issues here.  If I may:
(1) Is DROP TABLE <foo> acceptable by the standard?(2) Should we break "old" functionality?(3) assuming we support the
oldsyntax:     should DROP TABLE <foo> be functionally the same as     DROP TABLE <foo> RESTRICT(4) does that mean that
Thata DROP TABLE <foo> RESTRICT fails on
 
foreign key reference to foo.

Answers from my experience and from my reading of the standard.  (See earlier
note, I encourage you to determine if I am mistaken, the stand is often hard to
read.)
(1) It is ONLY acceptable (see conformance note) if you do not support
CASCADE.  If you support CASCADE, you must indicate CASCADE or RESTRICT.  This
isn't an "optional parameter".  So, no -- the suggestion that "DROP TABLE <foo>"
is now valid syntax given the CASCADE functionality breaks the standard.
Vendors <sarcasm> occasionally </sarcasm> decide to break the standard.
(currently the standards node seems to be down -- I was going to verify that
nothing in 2004 has yet to change this syntax.  That verification will have to
come tomorrow (assuming it comes back up).)
(2) I am new here.  This is really an answer that should be driven by
the user community.  My experience doing database engineering allows me to argue
both viewpoints.  I would claim, as I have seen others hint at, that "drop
table" operations are not heavily embedded into application code and this is one
case where backward compatibility may not be as important as clarity and
standard conformance.  Tom seems to have made a clear statement that there is A
DESIRE to not break old implementations directly with a syntax restriction.  I
don't know if you have a mechanism to provide a warning that this is a
deprecated feature -- if so, that may be "a good idea"(tm).  The reasoning
behind the standard thrust here is that a maintainer should explicitly know what
the drop command will accomplish.
(3) I believe Tom is right in that you don't want to do something "half
way".  It should behave like RESTRICT.  There are commercial examples for this
behavior (Oracle Rdb (aka Digital Rdb) is the one that immediately comes to
mind; Oracle 9i's CASCADE CONSTRAINTS|<nothing> is another variant).
(4) yes seems to be the general answer from all corners.  (I agree)

Dana
(BTW: while I mentioned this to some of the core folks, I actually do
participate on the SQL standard for the US.  NO, that doesn't make me the last
word when it comes to the standard (that's Jim Melton's job :-), but I do have
access to all the documents.  I have been developing commercial DB engines for
some so, I have some experience in the field but I am only just starting to hack
PostgreSQL.)

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wednesday, July 10, 2002 11:25 PM
To: Tom Lane
Cc: Jan Wieck; Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > As far as this question, seems with no RESTRICT/CASCADE, it fails, with
> > RESTRICT it drops the trigger, and with CASCADE it drops the referencing
> > table.  Is that accurate?
> 
> Not at all.  CASCADE would drop the foreign key constraint (including
> the triggers that implement it), but not the other table.  In my mind
> the issue is whether RESTRICT mode should do the same, or report an
> error.
> 
> I'm not eager to accept the idea that DROP-without-either-option should
> behave in some intermediate fashion.  I want it to be the same as
> RESTRICT.

Sounds good to me, and I don't think we need to require RESTRICT just
because the standard says so.  Does the standard require RESTRICT for
every DROP or just drops that have foreign keys?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)