Constraints on Temp tables

Yesterday, I had an interesting conversation on temp tables:
All started with this question:  What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.

CREATE TABLE #MyFirstTable

(

MyFirstTable_PK int,

CONSTRAINT PK_#MyFirstTable PRIMARY KEY CLUSTERED (MyFirstTable_PK)

)

The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.



The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.

What is the method to overcome it? It is very simple. Specify the constraint without the name.

It is possible only when you create the column. (Either through CREATE TABLE or ALTER TABLE)
So the following statements are valid:

CREATE TABLE #MyFistTable

(

MyFistTable_PK int PRIMARY KEY CLUSTERED

)

ALTER TABLE #MyFistTable ADD MyString varchar(20) NOT NULL DEFAULT (''),

sys_CreateDate datetime DEFAULT(GETDATE()),

HasNULL int CHECK (HasNULL <2)

But if the column is already created, it is not possible to add the column because it requires ADD CONSTRAINT statement. You cannot execute ADD CONSTRAINT statement without a constraint name.

While working on this, I fund another interesting issue. Let’s look at the code first:

CREATE TABLE #MySecondTable

(

MySecondTable_PK int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

MyFistTable_PK int NOT NULL REFERENCES #MyFistTable(MyFistTable_PK)

)

INSERT #MySecondTable (MyFistTable_PK) VALUES(1)

INSERT #MySecondTable (MyFistTable_PK) VALUES(2)

SELECT * FROM #MySecondTable

SELECT * FROM #MyFistTable

I have added another table, where I have a foreign key. Then I am inserting two rows to the second table. Note that I haven’t added any rows to the first table yet.

What do you expect? Generally, you expect the insert statements to fail as there is no corresponding entry in the first table. But unfortunately the results are different.

Do you know the reason? Check on the messages tab.

Do you see this message?

Skipping FOREIGN KEY constraint '#MySecondTable' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.