Re: Should this require CASCADE?

IMHO, I believe that the standard should be adhered to if at all possible.
Since Cascade was added, Restrict must be the default is my reading of the
standard.

So that everyone can talk from the same sheet, the 1999 SQL Standard for DROP
TABLE follows:


11.20 <drop table statement>
Function
Destroy a table.
Format
<drop table statement> ::=
DROP TABLE <table name> <drop behavior>
Syntax Rules
1) Let T be the table identified by the <table name> and let TN be that <table
name>.
2) The schema identified by the explicit or implicit schema name of the <table
name> shall include the descriptor of T.
3) T shall be a base table.
4) T shall not be a declared local temporary table.
5) If RESTRICT is specified, then T shall not have any subtables.
6) If RESTRICT is specified, then T shall not be referenced in any of the
following:
a) The <query expression> of any view descriptor.
b) The <search condition> of any table check constraint descriptor of any table
other than T or the <search condition> of a constraint descriptor of an
assertion descriptor.
c) The table descriptor of the referenced table of any referential constraint
descriptor of any table other than T.
d) The scope of the declared type of a column of a table other than T and of the
declared type of an SQL parameter of any SQL-invoked routine.
e) The <SQL routine body> of any SQL-invoked routine descriptor.
f) The scope of the declared type of an SQL parameter of any SQL-invoked
routine.
g) The trigger action of any trigger descriptor.
NOTE 197 - If CASCADE is specified, then such referenced objects will be dropped
by the execution of the <revoke statement> specified in the General Rules of
this Subclause.
7) Let A be the <authorization identifier> that owns the schema identified by
the <schema name> of the table identified by TN.
8) Let the containing schema be the schema identified by the <schema name>
explicitly or implicitly contained in <table name>.
Access Rules
1) The enabled authorization identifiers shall include A.
General Rules
1) Let ST be the <table name> of any subtable of T. The following <drop table
statement> is effectively executed without further Access Rule checking:
DROP TABLE ST CASCADE
2) If T is a referenceable table, then:
a) Let ST be structured type associated with T.
b) Let RST be the reference type whose referenced type is ST.
c) Let DT be any table whose table descriptor includes a column descriptor that
generally includes a field descriptor, an attribute descriptor, or an array
descriptor that includes a reference type descriptor RST whose scope includes
TN.
NOTE 198 - A descriptor that ''generally includes'' another descriptor is
defined in Subclause 6.2.4, "Descriptors", in ISO/IEC 9075-1.
d) Let DTN be the name of the table DT.
e) Case:
i) If DT is a base table, then the following <drop table statement> is
effectively executed without further Access Rule checking:
DROP TABLE DTN CASCADE
ii) Otherwise, the following <drop view statement> is effectively executed
without further Access Rule checking:
DROP VIEW DTN CASCADE
3) For every supertable of T, every superrow and every subrow of every row of T
is effectively deleted at the end of the SQL-statement, prior to the checking of
any integrity constraints.
NOTE 199 - This deletion creates neither a new trigger execution context nor the
definition of a new state change in the current trigger execution context.
4) The following <revoke statement> is effectively executed with a current
authorization identifier of ''_SYSTEM'' and without further Access Rule
checking:
REVOKE ALL PRIVILEGES ON TN FROM A CASCADE
5) Let R be any SQL-invoked routine whose routine descriptor contains the <table
name> of T in the <SQL routine body>. Let SN be the <specific name> of R. The
following <drop routine statement> is effectively executed without further
Access Rule checking:
DROP SPECIFIC ROUTINE SN CASCADE
6) For each direct supertable DST of T, the table name of T is removed from the
list of table names of direct subtables of DST that is included in the table
descriptor of DST.
7) The descriptor of T is destroyed.
Conformance Rules
1) Without Feature F032, ''CASCADE drop behavior'', a <drop behavior> of CASCADE
shall not be specified in <drop table statement>.



-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Wednesday, July 10, 2002 7:35 PM
To: Tom Lane
Cc: Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.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.

I think the idea was to have it default to CASCADE for this release, not
to break existing code right away. Then 7.3 is transition time and
RESTRICT will be the default from the next release on.

If so, this has to go into the release notes.


Jan

> 
> So if you prefer, imagine same example but you merely say
>         DROP TABLE foo;
> Does your answer change?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html