June 11, 2010 at 3:16 pm
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
June 12, 2010 at 2:59 am
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
June 14, 2010 at 9:04 am
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.
June 15, 2010 at 5:39 am
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
June 16, 2010 at 7:49 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply