|
|
You're in the
sql server programming
group:Can't Truncate table
sql server programming:
I have foreign keys set up on a table, which I disabled via: ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL When I view the results of EXEC sp_helpconstraint <MyTable> I can see that the status_enable for all FK constraints is "Disabled" However, when I run TRUNCATE Table <MyTable> I get an error message: "Can't Truncate <MyTable> because it is being referenced by a FOREIGN KEY Constraint." As an extra measure, I disabled all constraints on every table (NOCHECK) Then I performed a generate sql script on all tables and all foreign key constraints are NOCHECK. Yet, I still can't truncate MyTable. Is there something I'm missing?
Ok, then I will do that and recreate them.
But, is this response wrong then? Or am I misunderstanding it? http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/acf1876ea03d2f0e/6432666afd587f44?lnk=st&q=how+to+truncate+table+referenced+by+foreign+key+constraint+sql+server&rnum=2#6432666afd587f44 *********************** Question ******************************** When using truncate table statement on a table with foreign key relationship will cause an error like, Server: Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'purchase' because it is being referenced by a FOREIGN KEY constraint. Does anyone know any T-SQL command to disable/enable the constraint(s)? ******************************** Response ******************************** Newsgroups: microsoft.public.sqlserver.server From: "Kalen Delaney" <kalen_dela...@hotmail.please_reply_on_public_forums.com> Date: 2000/08/08 Subject: Re: How to truncate table with foreign key referenced Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author exec sp_helpconstraint purchase This will tell which table is referencing purchase ALTER TABLE <referencing table name> NOCHECK CONSTRAINT ALL This will disable ALL constraints on that table. Alternatively, you could look up the actual name of the constraint in the other table. ******************************** [quoted text, click to view] "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:uvmHWIMlHHA.3996@TK2MSFTNGP06.phx.gbl... > Sorry, I should have said: You must drop all the FOREIGN KEY constraints > that reference a table before you are able to TRUNCATE it. > > Tom > > "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message > news:OmPxx9LlHHA.3496@TK2MSFTNGP03.phx.gbl... >> You must drop all the constraints that reference a table before you are >> able to TRUNCATE it. Disabling them and/or marking them NOCHECK is not >> enough. >> >> Tom >> >> <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >>>I don't get this. >>> >>> I have foreign keys set up on a table, which I disabled via: >>> >>> ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL >>> >>> When I view the results of EXEC sp_helpconstraint <MyTable> >>> >>> I can see that the status_enable for all FK constraints is "Disabled" >>> >>> However, when I run TRUNCATE Table <MyTable> I get an error message: >>> >>> "Can't Truncate <MyTable> because it is being referenced by a FOREIGN >>> KEY Constraint." >>> >>> As an extra measure, I disabled all constraints on every table (NOCHECK) >>> >>> Then I performed a generate sql script on all tables and all foreign key >>> constraints are NOCHECK. >>> Yet, I still can't truncate MyTable. >>> >>> Is there something I'm missing? >>> >>> >> >> > >
Hi -
Wow, that was a long time ago! The answer was incomplete. It looks like I read the post too quickly and was just answering the specific question that was asked: "Does anyone know any T-SQL command to disable/enable the constraint(s)?" I apologize for the confusion. Tom is right, you have to drop the referencing constraint in order to truncate the table. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com [quoted text, click to view] <-> wrote in message news:Oxft8lMlHHA.1340@TK2MSFTNGP04.phx.gbl... > Ok, then I will do that and recreate them. > > But, is this response wrong then? Or am I misunderstanding it? > > http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/acf1876ea03d2f0e/6432666afd587f44?lnk=st&q=how+to+truncate+table+referenced+by+foreign+key+constraint+sql+server&rnum=2#6432666afd587f44 > > *********************** Question ******************************** > When using truncate table statement on a table with foreign key > relationship > will cause an error like, > > Server: Msg 4712, Level 16, State 1, Line 1 > Cannot truncate table 'purchase' because it is being referenced by a > FOREIGN > KEY constraint. > > Does anyone know any T-SQL command to disable/enable the constraint(s)? > > ******************************** Response ******************************** > > Newsgroups: microsoft.public.sqlserver.server > From: "Kalen Delaney" > <kalen_dela...@hotmail.please_reply_on_public_forums.com> > Date: 2000/08/08 > Subject: Re: How to truncate table with foreign key referenced > Reply to author | Forward | Print | Individual message | Show original | > Report this message | Find messages by this author > exec sp_helpconstraint purchase > > This will tell which table is referencing purchase > > > ALTER TABLE <referencing table name> NOCHECK CONSTRAINT ALL > > > This will disable ALL constraints on that table. Alternatively, you could > look up the actual name of the constraint in the other table. > > > ******************************** > > > > > > "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message > news:uvmHWIMlHHA.3996@TK2MSFTNGP06.phx.gbl... >> Sorry, I should have said: You must drop all the FOREIGN KEY constraints >> that reference a table before you are able to TRUNCATE it. >> >> Tom >> >> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >> news:OmPxx9LlHHA.3496@TK2MSFTNGP03.phx.gbl... >>> You must drop all the constraints that reference a table before you are >>> able to TRUNCATE it. Disabling them and/or marking them NOCHECK is not >>> enough. >>> >>> Tom >>> >>> <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >>>>I don't get this. >>>> >>>> I have foreign keys set up on a table, which I disabled via: >>>> >>>> ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL >>>> >>>> When I view the results of EXEC sp_helpconstraint <MyTable> >>>> >>>> I can see that the status_enable for all FK constraints is "Disabled" >>>> >>>> However, when I run TRUNCATE Table <MyTable> I get an error message: >>>> >>>> "Can't Truncate <MyTable> because it is being referenced by a FOREIGN >>>> KEY Constraint." >>>> >>>> As an extra measure, I disabled all constraints on every table >>>> (NOCHECK) >>>> >>>> Then I performed a generate sql script on all tables and all foreign >>>> key >>>> constraints are NOCHECK. >>>> Yet, I still can't truncate MyTable. >>>> >>>> Is there something I'm missing? >>>> >>>> >>> >>> >> >> > > > >
You must drop all the constraints that reference a table before you are able
to TRUNCATE it. Disabling them and/or marking them NOCHECK is not enough. Tom [quoted text, click to view] <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >I don't get this. > > I have foreign keys set up on a table, which I disabled via: > > ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL > > When I view the results of EXEC sp_helpconstraint <MyTable> > > I can see that the status_enable for all FK constraints is "Disabled" > > However, when I run TRUNCATE Table <MyTable> I get an error message: > > "Can't Truncate <MyTable> because it is being referenced by a FOREIGN KEY > Constraint." > > As an extra measure, I disabled all constraints on every table (NOCHECK) > > Then I performed a generate sql script on all tables and all foreign key > constraints are NOCHECK. > Yet, I still can't truncate MyTable. > > Is there something I'm missing? > >
Sorry, I should have said: You must drop all the FOREIGN KEY constraints
that reference a table before you are able to TRUNCATE it. Tom [quoted text, click to view] "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:OmPxx9LlHHA.3496@TK2MSFTNGP03.phx.gbl... > You must drop all the constraints that reference a table before you are > able to TRUNCATE it. Disabling them and/or marking them NOCHECK is not > enough. > > Tom > > <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >>I don't get this. >> >> I have foreign keys set up on a table, which I disabled via: >> >> ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL >> >> When I view the results of EXEC sp_helpconstraint <MyTable> >> >> I can see that the status_enable for all FK constraints is "Disabled" >> >> However, when I run TRUNCATE Table <MyTable> I get an error message: >> >> "Can't Truncate <MyTable> because it is being referenced by a FOREIGN KEY >> Constraint." >> >> As an extra measure, I disabled all constraints on every table (NOCHECK) >> >> Then I performed a generate sql script on all tables and all foreign key >> constraints are NOCHECK. >> Yet, I still can't truncate MyTable. >> >> Is there something I'm missing? >> >> > >
Aah, no problem.
When I first encountered the error message, I ran to google and tried out the first link I got (yours). Hehe. Thanks. [quoted text, click to view] "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:efAWeTNlHHA.492@TK2MSFTNGP04.phx.gbl... > Hi - > > Wow, that was a long time ago! > > The answer was incomplete. It looks like I read the post too quickly and > was just answering the specific question that was asked: > "Does anyone know any T-SQL command to disable/enable the constraint(s)?" > > I apologize for the confusion. Tom is right, you have to drop the > referencing constraint in order to truncate the table. > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > http://sqlblog.com > > > <-> wrote in message news:Oxft8lMlHHA.1340@TK2MSFTNGP04.phx.gbl... >> Ok, then I will do that and recreate them. >> >> But, is this response wrong then? Or am I misunderstanding it? >> >> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/acf1876ea03d2f0e/6432666afd587f44?lnk=st&q=how+to+truncate+table+referenced+by+foreign+key+constraint+sql+server&rnum=2#6432666afd587f44 >> >> *********************** Question ******************************** >> When using truncate table statement on a table with foreign key >> relationship >> will cause an error like, >> >> Server: Msg 4712, Level 16, State 1, Line 1 >> Cannot truncate table 'purchase' because it is being referenced by a >> FOREIGN >> KEY constraint. >> >> Does anyone know any T-SQL command to disable/enable the constraint(s)? >> >> ******************************** Response >> ******************************** >> >> Newsgroups: microsoft.public.sqlserver.server >> From: "Kalen Delaney" >> <kalen_dela...@hotmail.please_reply_on_public_forums.com> >> Date: 2000/08/08 >> Subject: Re: How to truncate table with foreign key referenced >> Reply to author | Forward | Print | Individual message | Show original | >> Report this message | Find messages by this author >> exec sp_helpconstraint purchase >> >> This will tell which table is referencing purchase >> >> >> ALTER TABLE <referencing table name> NOCHECK CONSTRAINT ALL >> >> >> This will disable ALL constraints on that table. Alternatively, you could >> look up the actual name of the constraint in the other table. >> >> >> ******************************** >> >> >> >> >> >> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >> news:uvmHWIMlHHA.3996@TK2MSFTNGP06.phx.gbl... >>> Sorry, I should have said: You must drop all the FOREIGN KEY >>> constraints >>> that reference a table before you are able to TRUNCATE it. >>> >>> Tom >>> >>> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >>> news:OmPxx9LlHHA.3496@TK2MSFTNGP03.phx.gbl... >>>> You must drop all the constraints that reference a table before you are >>>> able to TRUNCATE it. Disabling them and/or marking them NOCHECK is not >>>> enough. >>>> >>>> Tom >>>> >>>> <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >>>>>I don't get this. >>>>> >>>>> I have foreign keys set up on a table, which I disabled via: >>>>> >>>>> ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL >>>>> >>>>> When I view the results of EXEC sp_helpconstraint <MyTable> >>>>> >>>>> I can see that the status_enable for all FK constraints is "Disabled" >>>>> >>>>> However, when I run TRUNCATE Table <MyTable> I get an error message: >>>>> >>>>> "Can't Truncate <MyTable> because it is being referenced by a FOREIGN >>>>> KEY Constraint." >>>>> >>>>> As an extra measure, I disabled all constraints on every table >>>>> (NOCHECK) >>>>> >>>>> Then I performed a generate sql script on all tables and all foreign >>>>> key >>>>> constraints are NOCHECK. >>>>> Yet, I still can't truncate MyTable. >>>>> >>>>> Is there something I'm missing? >>>>> >>>>> >>>> >>>> >>> >>> >> >> >> >> > >
Does 'DELETE FROM MyTable' work?
MH [quoted text, click to view] <-> wrote in message news:uBmiJhLlHHA.4628@TK2MSFTNGP06.phx.gbl... >I don't get this. > > I have foreign keys set up on a table, which I disabled via: > > ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL > > When I view the results of EXEC sp_helpconstraint <MyTable> > > I can see that the status_enable for all FK constraints is "Disabled" > > However, when I run TRUNCATE Table <MyTable> I get an error message: > > "Can't Truncate <MyTable> because it is being referenced by a FOREIGN KEY > Constraint." > > As an extra measure, I disabled all constraints on every table (NOCHECK) > > Then I performed a generate sql script on all tables and all foreign key > constraints are NOCHECK. > Yet, I still can't truncate MyTable. > > Is there something I'm missing? > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |