Re: Truncate one table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 17:04:16 +0100

The error speaks for itself. If a table is referenced by a foreign key constraint, you cannot use TRUNCATE
TABLE. Either do:

DELETE FROM tblname

Or remove the foreign key, truncate the table and add back the foreign key. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaygo" <jaygo999.breath@hotmail.com> wrote in message news:c3sb3f$2abufh$1@ID-227643.news.uni-berlin.de...
> Thanks Vishal
>
> That worked well on a test of one table when I tried to run the script on
> another
> table I got an error "Cannot truncate "table_name" because it is being
> referenced
> by a foreign key constraint". The last bit I tested in Northwind and was
> going to restore
> from backup after the operation completed.
>
> Any help appreciated.
>
> John
> "Vishal Parkar" <REMOVE_THIS_vgparkar@yahoo.co.in> wrote in message
> news:eo$m2xaEEHA.240@tk2msftngp13.phx.gbl...
> > run command "truncate table"
> >
> > truncate table <table_name> (This will empty complete table and preserve
> table structure.)
> >
> > -- 
> > Vishal Parkar
> > vgparkar@yahoo.co.in
> >
> >
>
>

Relevant Pages

  • Re: truncate all tables
    ... You can't truncate a table if it is referenced by a foreign key. ... Disabling the foreign key constraint or emptying the referencing ... > Thanks for any help Mikey ...
    (microsoft.public.sqlserver.programming)
  • why drop constraints before truncate
    ... ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesCustomer ... TRUNCATE TABLE dbo.SalesStage ... CONSTRAINT FK_SalesCustomer FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Truncate one table
    ... > The error speaks for itself. ... If a table is referenced by a foreign key ... constraint, you cannot use TRUNCATE ...
    (microsoft.public.sqlserver.programming)
  • RE: loading text comma delimited data
    ... You can't truncate a table with foreign key constraints on it. ... but the one table with foreign key contraints will not be dropped ... or should I even worry about truncating data before the data load. ...
    (microsoft.public.sqlserver.dts)
  • Re: Optimiztion question
    ... If you don't specify WITH CHECK, the ALTER TABLE statement will essentially run in zero time since all it needs to do is eamine and change a few rows in the meta data. ... Create Table fooChild (au_id int, title_id int, royaltyper int, ... Constraint FK_fooChild_fooParent Foreign Key References fooParent, ... A foreign key that is added later will not start off as trusted unless you use the WITH CHECK option when creating the foreign key constraint. ...
    (microsoft.public.sqlserver.programming)