FOREIGN KEY constraint

Question

FOREIGN KEY constraint

harita

SSCrazy

Points: 2197
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

harita SSCrazy Points: 2197 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

harita SSCrazy Points: 2197 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

harita SSCrazy Points: 2197 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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