Update. Since the version 2016 SQL Server supports anonymous constraint declaration:
CREATE TABLE #temp_table ( id1 int NOT NULL, id2 int NOT NULL, name1 nvarchar(100), name2 nvarchar(100), PRIMARY KEY (id1, id2), UNIQUE (name1, name2) );
For earlier versions the solution is below.
Local temporary tables are isolated in the scope of current session regardless its name. It means that #temp_table
in the session 1 is not the same that #temp_table
in the session 2. However, the names of constraints are stored in the metadata of tempdb
and can produce duplicate name errors.
CREATE TABLE #temp_table ( id int NOT NULL, name nvarchar(100) CONSTRAINT PK_temp_table PRIMARY KEY (id) )
Open SSMS and run the code in the first session. It's OK. If you open the second session and run it again, the following error will be raised.
Msg 2714, Level 16, State 5, Line 1 There is already an object named 'PK_temp_table' in the database.
The first solution is simple but it works only:
- for constraints that can be defined inline with the column (primary key, defaults, checks)
- in case of simple key only
This code is OK in multi-sessions.
CREATE TABLE #temp_table ( id int NOT NULL PRIMARY KEY, name nvarchar(100) )
What do we do in the case of a composite primary key or other constraint types like UNIQUE? Fortunately, the dynamic SQL can do the job!
CREATE TABLE #temp_table ( id1 int NOT NULL, id2 int NOT NULL, name nvarchar(100) ); DECLARE @pk_name sysname, @sql nvarchar(max); SET @pk_name = 'PK_' + replace(convert(nvarchar(38), newid()), '-', '_'); SET @sql = 'ALTER TABLE #temp_table ADD CONSTRAINT ' + @pk_name + ' PRIMARY KEY (id1, id2)'; EXEC sp_executesql @sql;
Note that table index doesn't require to have a globally (database scope) unique name because it should be unique in the scope of the table only. Hence, the following code is OK in multi-sessions.
CREATE TABLE #temp_table ( id int NOT NULL PRIMARY KEY, name nvarchar(100) ); CREATE INDEX IX1_temp_table ON #temp_table (name);
Comments
SQL 2016 improvements
Submitted by Richard (not verified) on
From SQL 2016 onward it's possible to do the following, so you don't get the issue with a composite primary key:
MSSQL 2016
Submitted by st on
Exactly, since 2016 version constraint can be declared without name as you wrote in the example. Thank you for appreciations.
What about the inline unique
Submitted by Bipul (not verified) on
What about the inline unique/default constraints?
Unique constraints supported
Submitted by st on
Unique constraints are supported, too. This code works in SQL Server 2016 version.