Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Remove all tables for user with one SQl statement?

Re: Remove all tables for user with one SQl statement?

From: Robert Klemme <bob.news_at_gmx.net>
Date: Mon, 19 Dec 2005 16:40:36 +0100
Message-ID: <40o2jlF1b83f4U1@individual.net>


Thomas Kellerer wrote:
> On 19.12.2005 13:19 Robert Klemme wrote:
>>> A "DROP TABLE xyz CASCADE CONSTRAINTS" will remove all constraints
>>> on the table as well, including foreign keys.
>>>
>>> So the order shouldn't actually matter in that case, shouldn't it?
>>
>>
>> And what do you do if a table X referencing table A you're about to
>> delete references it?
>
> But isn't "CASCADE CONSTRAINTS" intended to handle that:
>
> The following script works without problems:
>
> create table a (a_id integer primary key);
> create table x
> (
> x_id integer primary key,
> fk_id integer references a(a_id)
> );
>
> insert into a values (1);
> insert into x values (2,1);
>
> commit;
>
> drop table x cascade constraints;
>
> I can also drop table a at the end (without dropping table x).
>
> Even if I create another table referencing a, I can drop table a at
> any time without dropping any table referencing it.
>
> So in which situation would a DROP using CASCADE CONSTRAINTS fail?

IMHO there's a problem if the user dropping the table has no permissions to drop those constraints.

    robert Received on Mon Dec 19 2005 - 09:40:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US