1. tempdb naming scope for Primary Key constraints *global* on local #temptables
I wish to report and share very counter-intuitive and IMHO un-desirable
behavior in SQL6.5 and SQL7.0. Perhaps someone "in the know" can explain the
reasoning behind this 'feature'.
I know this is not a bug but nevertheless a very inconvenient feature as far
as I can see:
please consider the following T-SQL code snip
create table #InParamsDate
CompanyCode char(12) not null default '',
PeriodEndDate datetime not null default '1 Jan 1800 12:00am'
alter table #InParamsDate
add constraint TT_InParamsDate__PK primary key nonclustered
Note the primary key constraint name is TT_InParamsDate__PK ---- for future
If you now go and execute this code in one QueryAnalyser session, then all
will be well. Now go and execute the same code in a second session (leave
the first one running) and you will get the following error:
Server: Msg 2714, Level 16, State 4, Line 7
There is already an object named 'TT_InParamsDate__PK' in the database.
Server: Msg 1750, Level 16, State 1, Line 7
Could not create constraint. See previous errors.
This occurs because the name of the primary key *constraint* is *global* to
the tempdb even though it is hanging off the *session local* temporary
table. I find this very counter-intuitive and un-desirable behavior and
would like to know why it works this way. We now have to change chunks of
code to generate names for constraints dynamically and then use dynamic SQL
Perhaps this behavior could be improved in a near future version of SQL
Server. I tried it on SQL7.0 SP1 beta and it behaves the same way as 6.5 and
Looking for some enlightenment
thanks in advance
Yuri Budilov (always expressing personal opinions only)
2. Delphi 3(SR2) & Access97
3. Primary key constraints vs Unique constraints
4. UDF Input Datatype question
5. finding the column name of a Primary Key
6. How to understand this query?
7. Finding the Primary Key Column name programatically?
8. Finding the field name of the primary key
9. Changing Primary Key Values when foreign key constraints exist
10. BCP INTO A TABLE THAT CONTAINS PRIMARY KEY AND FOREIGN KEY CONSTRAINTS
11. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object