September 30, 2009 at 1:29 am
I am trying to remove records from a large database by truncating the tables.
However, I have run into issues with Foriegn Key constriaints.
I have tried to disable the FK constraints temporarily so that i can remove the records but the usual syntax appears not to work.
The table tblProgFundingCategory has 3 FK one of which is FK_tblProgFundingYearCategory_tblProgFundingCategory
It is a concatenated FK
running
ALTER TABLE [dbo].[tblProgFundingCategory] NOCHECK CONSTRAINT [FK_tblProgFundingYearCategory_tblProgFundingCategory]
--and the other two alter statements
ALTER TABLE [dbo].[tblProgFundingCategory] NOCHECK CONSTRAINT FK_tblProgFundingCategory_tblProg
ALTER TABLE [dbo].[tblProgFundingCategory] NOCHECK CONSTRAINT [FK_tblProgFundingCategory_tlkpFundingCategory]
-- and then the truncate
truncate table [dbo].[tblProgFundingCategory]
go
-- gets me this
Error:
Cannot truncate table 'dbo.tblProgFundingCategory' because it is being referenced by a FOREIGN KEY constraint.
I think it is the concatenated key which is causing the issues but don't know how to get around it.
any help appreciated
Regards
Erick
September 30, 2009 at 1:46 am
ErickTreeTops (9/30/2009)
I have tried to disable the FK constraints temporarily so that i can remove the records but the usual syntax appears not to work.
You have to drop the constraint in order to truncate the table. It's not sufficient to disable the constraint, it has to be removed completely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2009 at 5:22 am
You are going to truncate the Table tblProgFundingCategory. So you need to drop the foreign keys of some other tables those are having references on the Primary key of your tblProgFundingCategory Table. You are supposed to alter those tables not tblProgFundingCategory Table. You can find those foreign keys and tables by querying
Select
O.name as ConstraintName,
Object_name(O.Parent_object_id) as ObjectName
from
sys.foreign_keys F,
sys.objects O
Where
F.name=O.name AND
F.referenced_object_id=object_id('tblProgFundingCategory')
Regards,
Sanoj
..
September 30, 2009 at 5:16 pm
Sanoj,
I used your script to return the FK which return [FK_tblProgFundingYearCategory_tblProgFundingCategory]
My new alter statement was then
ALTER TABLE [dbo].[tblProgFundingYearCategory] NOCHECK CONSTRAINT [FK_tblProgFundingYearCategory_tblProgFundingCategory]
after i ran
truncate table [dbo].[tblProgFundingCategory]
it i got the same error message
Cannot truncate table 'dbo.tblProgFundingCategory' because it is being referenced by a FOREIGN KEY constraint.
I will try Gail Shaw's reply and see if indeed you must drop and not just disable constraints for truncate to work
October 1, 2009 at 1:17 am
I meant to say
ALTER TABLE [dbo].[tblProgFundingYearCategory] DROP CONSTRAINT [FK_tblProgFundingYearCategory_tblProgFundingCategory]
..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply