May 12, 2008 at 5:54 am
Hi All
I have been using SP that creates a temp table with promary key constraints, this SP was running untill last week, i got a prolem last week saying that the constraint already exists and cannot create a new ones, i have cleared that constraint and it started to work again, has any one faced problmes like this before?? how do i handle this in future??, the SP is been used by a java application. i am using a 64 bit SQL 2005
thanks in advence
Cheers
Suji
May 12, 2008 at 6:37 am
Don't name constraints created on temp tables. Object names (including constraint names) must be unique.
When you create a temp table, SQL puts a hash value on the end to ensure there won't be a duplicate. That doesn't happen if you explicitly name a constraint. If there's an existing constrain with the same name (even if it's on a separate table) the proc will throw an error.
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
May 12, 2008 at 6:47 am
Thanks Gial
there is a constraint with the same name on other SP as well, but its working on fine now with the same name when it is supposed to trow error(i might be wrong in understanding the concept), how is that possible??
May 12, 2008 at 6:59 am
You'll get an error if a constraint exists and you try to create another with the same name.
Since they're on Temp Tables and the constraint is dropped when the table is dropped, whether or not you get an error depends on how long the temp tables exist.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply