Re: how to boost drop table statement

Thomas Kellerer wrote:
>
> John Currier wrote on 18.06.2005 20:22:
> >>You could try to TRUNCATE the tables first, but then they must not have
> >>foreign key constraints.
> >
> >
> > You just have to do them in the correct order (assuming no recursive
> > constraints).
> >
> > <plug>
> > SchemaSpy grew out of a tool that I wrote to determine that order.
> > It'll generate insertion order and deletion order lists of tables that
> > are intended to be used by load/purge scripts.
> > </plug>
> >
>
> No that won't help. There is a limitation on the usage of TRUNCATE with
> Oracle.
>
> Quote from the manual (9.2):
>
> "You cannot truncate the parent table of an enabled referential integrity
> constraint. You must disable the constraint before truncating the table."
>
> Even if the order is right, it won't help here.

Assuming no cross-referencing referential integrity, ordering would truncate the table
containing the FK reference first, thus it would seem to work.

Apparently, Oracle doesn't support the CASCADE option for DROP, which would eliminate
the similar ordering problem when using a list of DROP TABLEs. It also solves the
cross-referencing problem. Lack of standards support does have its consequences ;^)

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
.

Relevant Pages

  • Re: DTS in SQL 2005 on tables with constraints
    ... constraints in between running the scripts and actually generating the ... script to have the ALTER TABLEs to reinstate the constraints is a pain ... I thought that EXEC sp_msforeachtable "ALTER TABLE ... NOCHECK CONSTRAINT all" might do the trick, but you can't TRUNCATE ...
    (microsoft.public.sqlserver.dts)
  • Re: truncate table - with foreign key constraints
    ... TRUNCATE is simply not allowed with foreign key constraints in place. ... You'll need to go through the trouble for dropping and recreating the ...
    (microsoft.public.sqlserver.server)
  • Re: resetting seed
    ... Read about constraints in the BOL. ... CREATE TABLE MyCustomers PRIMARY KEY, ... I have to reset the identity seed on one of my SQL Server CE tables. ... only TRUNCATE TABLE is my option. ...
    (microsoft.public.sqlserver.ce)
  • Disabling Constraint for Bulk Loading
    ... For this i want to disable all the constraints on all the user ... fetch next from c1 into @tablename ... Now when i try to truncate one of the tables (say titles) it gives me ...
    (comp.databases.ms-sqlserver)
  • truncate table - with foreign key constraints
    ... I have a database with foreign key constraints. ... truncate tables to perform data loads. ...
    (microsoft.public.sqlserver.server)