Posted by Erland Sommarskog on 05/16/07 14:15

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: how to if needed recover from mistake
• Prev in forum: Re: /usr/sbin/mysqld: unknown variable 'expire_logs_days=10'
• Thread view: Re: create constraint error

[Reply to this message]