Naming Constraints on Temp Tables
Published: Nov 06, 2015
This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well.

It involves the use of named constraints within temp tables.

Essentially what was happening was that someone had some code and was, quite understandably, following their normal guidelines for naming a constraint on a table when they created it.

The problem was that this code was called with a very high frequency and suddenly the application started to error.

The reason was simply down to the convention of naming your constraints.

Now, everyone knows (and if they don’t then they should), that it’s good practice to name your constraints whether they be keys, indexes etc as follows:

create table constraintNameTable
(
  
id int identity(1, 1) not null,
  
data varchar(100) not null,
  
miscValue varchar(20) not null,
  
constraint pk_constraintNameTable primary key clustered(id)
)

create table constraintNameTable2
(
  
id int identity(1, 1) not null,
  
data varchar(100) not null,
  
miscValue varchar(20) not null
)
alter table constraintNameTable2
  
add constraint pk_constraintNameTable2 primary key clustered(id)


So why wouldn’t you do this in a temporary table? Well it’s actually down to the fact that although your temp table name is uniquified when placed into tempDB:

create table #temp(id int)

select name
from tempDB.dbo.sysobjects
where name like '#temp%'

drop table #temp


SQL Server does NOT do the same process with the constraint but actually stores it as per the name you allocated.

Therefore if you create another temp table with the same constraint then you’re likely to get an error.

Run the following code in SSMS:

create table #temp
(
  
id int,
  
constraint pk_temp primary key clustered(id)
)


Now, in a new window run exactly the same code and you should see the following:

As you can see, although SQL Server would have happily created the table for you because it uniquifies the name, it hits an immediate problem with the constraint.

To get around this, simply allow SQL Server to generate the name for you. I do stress that you should only really do this for temp table constraints because I think all constraints on normal tables should be sensibly named.

For a primary key:

create table #temp
(
  
id int,
  
primary key clustered(id)
)


Or a clustered index which isn’t the primary key:

create table #temp
(
  
id int,
  
unique clustered(id)
)


Just something to note in case you come across the same issue.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron