Re: truncate table - with foreign key constraints

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/03/04 

Date: Tue, 2 Nov 2004 22:39:28 -0600

TRUNCATE is simply not allowed with foreign key constraints in place.
You'll need to go through the trouble for dropping and recreating the
constraints in order to use TRUNCATE. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Zev Hochberg" <hochberg@ncbi.nlm.nih.gov> wrote in message 
news:fe1b0fd8.0411021551.1101191@posting.google.com...
>I have a database with foreign key constraints.  I therefore can't
> truncate tables to perform data loads.
>
> Is there an easy way to tell the system to ignore these constraints
> for the term of a data load or truncation of data?
>
> I prefer not to have to remove the constraints, truncate tables, and
> recreate constraints

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: how to boost drop table statement
    ... >>>You could try to TRUNCATE the tables first, but then they must not have ... >>>foreign key constraints. ... Assuming no cross-referencing referential integrity, ... * FirstSQL/J Object/Relational DBMS ...
    (comp.lang.java.databases)
  • truncate table - with foreign key constraints
    ... I have a database with foreign key constraints. ... truncate tables to perform data loads. ...
    (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)
Loading