Re: SQL: DELETE and DROP TABLE with cascading ?

On Feb 25, 7:38 pm, clau..._at_wonderworld.com (Claudia d'Amato) wrote:
> As far as I found out DELETE and DROP TABLE are NOT possible with some kind of
> additional cascading instruction.
>
> DELETE CASCADE
> FROM mytable
> WHERE ....
>
> is not possible.
> Same with:
>
> DROP TABLE mytable CASCADE
>
> The only way I found so far is that I have to specify a clause
> "ON DELETE CASCADE" at the time when the table(s) were created.
>
> Is this really true?
>
> Is there no work around to pass an explicitly "get rid aff the whole table structure/records"
> at the time of deletion regardless of wether an ON DELETE CASCADE is specified before or not ?
>
> Claudia

Would be helpful if you explain what you want to achieve... Anyway, the options you have are these:

if foreign key constraints referencing the table being deleted from are specified with ON DELETE CASCADE, then normal DELETE will automatically get rid of all dependent rows in referencing tables. ON DELETE SET NULL will set deleted values in referencing tables to NULL. Default behavior is to disallow deletion of primary/unique key values if there are dependent rows in referencing tables. DROP TABLE CASCADE CONSTRAINTS is also possible, but this time dropping of constraints is cascaded (that is, constraints referencing primary/unique keys of the table being dropped will also be dropped, but the data in referencing tables will not be deleted or altered.)

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Sun Feb 25 2007 - 12:12:35 CST