Named Constraints on Temp Tables

Momma sed don't name constraints on temp tables. But why?

How SQL Makes Temp Tables Globally Unique
 
When you first build a temp table, SQL stores the definition of that temp table away  and every subsequent time you create it, it's actually a clone of that one, original table definition. To allow each individual connection to create a clone of the table, it has to do something about the name so that it won't collide with itself. Let's see what that looks like

if object_id('tempdb.dbo.#test') is not null drop table #test

create table #test

(

    Id int primary key clustered

)

Here, I've created a pretty basic temp table, with an unnamed, primary key on the table. Lets look at the meta data in information_schema (you can use system catalogs also, but for illustration purposes, it's a little easier for me to use information_schema here).

    select table_name

    from tempdb.information_schema.tables

    where table_name like '#test%'

 

As you can see, the table you've created is not actually called "#test" but "#test___...__<someRandomJunk>" (if you run this locally, that  hex string at the end will be different for you). This is how SQL prevents table collisions between temp tables; by actually creating clones of the table, and hiding the implementation details from you so you can still interact with it, within your current session, by just referencing "#test"

Similarly, let's take a look at the constraints for this table

    select constraint_name

    from information_schema.table_constraints

    where table_name like '#test%'

     

It added the same hexidecimal-type nonsense to the end of the constraint.

Named Constraints

 

So what happens if you try to name a constraint on a temp table. Let's rewrite the temp table like so:

    if object_id('tempdb.dbo.#test') is not null drop table #test

    create table #test

    (

        Id int

       

        constraint PKC__#test__id primary key clustered (id)

    )

Now when we look at the constraints, we don't see a constraint suffixed with a bunch of random hex, but the exact constraint name we typed in; namely "PKC__#test__id". But does that matter? You can re-run this statement till the cows come home and it will behave just fine. But now open a new connection and run the same statement.

Msg 2714, Level 16, State 5, Line 2

There is already an object named 'PKC__#test__id' in the database.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint or index. See previous errors.

Now we get an error. Why is that? Well remember when we created our primary key without a name, it created a key with a bunch of unique hex at the end. If you created that table 10 more times, you'd get 10 different unique names for the primary key (based on the hex at the end). But when you specify the constraint name, all that uniqueification SQL does gets thrown out the window and it assumes you meant to create a constraint, specifically called what you gave it. Unfortunately, that name is no longer unique for every instance of the temp table you create, and if you create a second connection, both tables, while themselves unique, will try to create the non-unique constraint with the same name; it will collide, and throw an error.

Alternatives

 

So what alternatives do you have? Well most constraints can be created anonymously (DEFAULT, CHECK, UNIQUE, PRIMARY KEY, etc) so while atrocious to read, the following is completely valid

if object_id('tempdb.dbo.#test') is not null drop table #test

create table #test

(

    Id int check(id < 1000000) default 0

   

    primary key clustered (Id),

    unique nonclustered (id)

)

And you can see all the constraints are named automatically and more importantly, uniquely, by SQL

Note, you can create named indexes on temp tables, and those will NOT collide with other sessions (I'm guessing because they're considered ancillary structures, and not part of the core table definition itself)

create nonclustered index ixn__#test on #test (id)

select top 1000 *

from sys.indexes

where object_id = object_id('tempdb.dbo.#test')

 

Summary

So while you CAN technically create a named constraint on a temp table, you should almost NEVER do it because in the process of naming it, you’re setting yourself up for failure if you ever want to create more than one of the temp tables. And if that’s a reasonable assumption to make, you should probably just create a permanent table.