Post reply

Foriegn Key constraint Removal

  • September 30, 2009 at 1:29 am

    #226686

    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

    #1059838

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 30, 2009 at 5:22 am

    #1059877

    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

    #1060242

    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

    #1060298

    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