Post reply

FOREIGN KEY constraint

  • March 17, 2014 at 7:36 am

    #286812

    Hi all,

    i created a SSIS package where ''execute sql task'' where the table 1 to 10 are truncated and i have issues with two tables and upload new data into the tables.

    [Execute SQL Task] Error: Executing the query "Truncate Table Table_7;

    Truncate Table Table_7..." failed with the following error: "Cannot truncate table 'Table_8' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    i need simple T-sql or solution , should i delete the constraint or edit the constraint and i have to recreate the constraint back

    Thank you all

  • March 17, 2014 at 7:42 am

    #1697635

    Switch to delete instead of truncate. Removing the constraint is risky, as it leaves a window where data can be inserted which would violate the constraint.

    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

  • March 17, 2014 at 7:46 am

    #1697636

    thank you Gail Shaw

    but the requirement demands only truncate not delete, what should be the work around

    i want to remove constraint and add it back after i upload the data

  • March 17, 2014 at 7:50 am

    #1697638

    And does that requirement have a reason other than 'because I said so'?

    You'll have to drop the constraint entirely and then hope you can add it back successfully. Be sure to add some error detection and notification for when the recreation of the constraint fails

    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

  • March 17, 2014 at 8:02 am

    #1697649

    i don't know the reason but, i cant delete the tables , is there any script i can use

  • March 17, 2014 at 8:08 am

    #1697653

    You need a script to drop the constraint and another script to enable it, then some component in your SSIS package which checks that the constraint was indeed recreated and notifies someone if it wasn't

    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

  • March 17, 2014 at 8:38 am

    #1697665

    yes that's the plans and i don't know how to do it and any script would be helpful

  • March 17, 2014 at 8:41 am

    #1697667

    Books Online (the SQL help file) -> ALTER TABLE

    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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply