Corrupt Constraints - How to get rid of

Question

Post reply

Corrupt Constraints - How to get rid of

andegre

SSCertifiable

Points: 5875
More actions
June 11, 2010 at 3:16 pm

#143583

I am trying to run a procedure that inserts records into a table that already has some foreign key constraints. In order to do the insert, I need to drop the constraints, add the records, then re-add the constraints (lost some production data and am trying to sync the table with the backup).
When running this script, it says the insert failed because of the foreign key constraint on x table with y column.
Well, that specific constraint doesn't exist anymore. I think I hosed it up while trying to get these records put back in, but now I'm stuck because I can't drop that constraint, I can't "add it again", and I can't view it (via Information_Schema or sys.foreign_keys).
Is anyone else aware of issues like this? Any way to get around this?
TIA

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Please run this an post the full results

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

What's the error that you get when you ty to drop 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

andegre SSCertifiable Points: 5875 More actions · Answer 2

Had a 40 minute window to run it in this morning before "business hours" and it didn't finish so I had to kill it. Will try again at my next window.

carnaud SSC Veteran Points: 266 More actions · Answer 3

Run “exec sp_who” and see if your script to drop/recreate the constraint is being blocked by anyone (including yourself).

It is possible you left a transaction open accidently. You can check to see if you have left any transactions open if you still have your query windows up with "SELECT @@TRANCOUNT". If the value returned is > 0, then you can "ROLLBACK TRANSACTION" until (SELECT @@TRANCOUNT) = 0

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

gregory.anderson (6/11/2010)
I am trying to run a procedure that inserts records into a table that already has some foreign key constraints. In order to do the insert, I need to drop the constraints, add the records, then re-add the constraints (lost some production data and am trying to sync the table with the backup).
When running this script, it says the insert failed because of the foreign key constraint on x table with y column.
Well, that specific constraint doesn't exist anymore. I think I hosed it up while trying to get these records put back in, but now I'm stuck because I can't drop that constraint, I can't "add it again", and I can't view it (via Information_Schema or sys.foreign_keys).
Is anyone else aware of issues like this? Any way to get around this?
TIA

When re-enabling the constraints at the end, make sure to use the "WITH CHECK CHECK" so the constraint ends up being trusted again, and thus able to be used for query optimization.