create constraint error

Hi.

I have a procedure with this in it (there are no other references to
asset_number_bak_tmp_pk in the procedure and it calls nothing else written by
me, just system calls or normal dml).

create table #asset_bak(
asset_number varchar(60) not null,
asset_desc varchar(100) null,
location varchar(40) null,
constraint asset_number_bak_tmp_pk primary key clustered (asset_number))

When I run the procedure, I get this message:

(1 row(s) affected)
Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19
There is already an object named 'asset_number_bak_tmp_pk' in the database.
Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19
Could not create constraint. See previous errors.

How can I find where else the system thinks this constraint exists?


I tried this but it only finds it in one place (one row in the result set),
i.e. my procedure:

select sysobjects.name, syscomments.text
from sysobjects, syscomments
where sysobjects.id = syscomments.id and
((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or
(lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))

Is this somehow a case where I need to do something dynamically, or purge some
information? I thought temp tables and their crony constraints disappeared
after the procedure exited.

thanks
Jeff Kish

Re: create constraint error

On Wed, 16 May 2007 08:41:50 -0400, Jeff Kish wrote:

>Hi.
>
>I have a procedure with this in it (there are no other references to
>asset_number_bak_tmp_pk in the procedure and it calls nothing else written by
>me, just system calls or normal dml).
>
>create table #asset_bak(
> asset_number varchar(60) not null,
> asset_desc varchar(100) null,
> location varchar(40) null,
> constraint asset_number_bak_tmp_pk primary key clustered (asset_number))
>
>When I run the procedure, I get this message:
>
>(1 row(s) affected)
>Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19
>There is already an object named 'asset_number_bak_tmp_pk' in the database.
>Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19
>Could not create constraint. See previous errors.
>
>How can I find where else the system thinks this constraint exists?
>
>
>I tried this but it only finds it in one place (one row in the result set),
>i.e. my procedure:
>
>select sysobjects.name, syscomments.text
>from sysobjects, syscomments
>where sysobjects.id = syscomments.id and
>((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or
> (lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))
>
>Is this somehow a case where I need to do something dynamically, or purge some
>information? I thought temp tables and their crony constraints disappeared
>after the procedure exited.
>
>thanks
>Jeff Kish
I got around the problem by removing the 'constraint name' clause.
I guess it is optional, but I still wonder is it 'wrong' to have a named
constraint on a temporary table, and shouldn't it of gone away with the table
at the end of procedure execution?

thanks for the illumination, shots, etc.
Jeff Kish

Re: create constraint error

Jeff Kish (jeff.kish@mro.com) writes:
> I got around the problem by removing the 'constraint name' clause. I
> guess it is optional, but I still wonder is it 'wrong' to have a named
> constraint on a temporary table, and shouldn't it of gone away with the
> table at the end of procedure execution?

Correct. For regular tables it is good practice to explicitly name your
constraints. But for temp tables you should never do it. The point with
temp tables is that two different sessions can create the same table without
conflict, because the real name behind the scenes is padded with some stuff
to make it unique. But if you name the constraints you lose this.

Keep in mind that all temp tables end up in sys.objects, and this table
(well view) also includes names of constraints.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx