Re: Truncate one table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jaygo (jaygo999.breath_at_hotmail.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 16:20:11 -0000

Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OqpgpmbEEHA.1368@TK2MSFTNGP11.phx.gbl...
> 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 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: 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: Regarding table Order
    ... "You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY ... constraint; instead, use DELETE statement without a WHERE clause." ...
    (comp.databases.ms-sqlserver)
  • 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)