Primary Key Violation while creating #Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Primary Key Violation while creating #Tables

Author

Topic

mallikarjunekmn
Starting Member

2 Posts

Posted - 2008-10-23 : 05:21:58

While creating #temp table with primary key , i am getting an error like

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_TMP_TABLE_CREATION' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Run this Script in two seperate query analyzer window
------------------------------------------------------
CREATE TABLE #TMP_TABLE_CREATION
(
[COLUMN1] [varchar](14) NOT NULL,
[COLUMN2] [float] NOT NULL,
[COLUMN3] [int] NOT NULL,
[COLUMN4] [float] NOT NULL,
CONSTRAINT [PK_TMP_TABLE_CREATION] PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC,
[COLUMN2] ASC,
[COLUMN3] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Concern
-------
If sql is allowing to create # tables no of times with the same name then why it is not allowing to create the primary key for the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 05:27:12

thats because you're giving same name for pk constraint in both the tables. change name of one of them and it will let you create both tables with pk constraints.This is because you cant have more than one constraint existing with same name.

mallikarjunekmn
Starting Member

2 Posts

Posted - 2008-10-23 : 06:07:36

I know that the primary key name is same. My concern is why , when allowing to create two #table with same table name like that y dont we have same primary key constraint name for that # table. Is that the restriction in sql server 2005 or is it some defect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 06:34:53

quote:
Originally posted by mallikarjunekmn

I know that the primary key name is same. My concern is why , when allowing to create two #table with same table name like that y dont we have same primary key constraint name for that # table. Is that the restriction in sql server 2005 or is it some defect.

its not a defect, it cant have two records with same name in sysconstraints table which is where it stores constraint info

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 07:28:26

quote:
Originally posted by mallikarjunekmn

I know that the primary key name is same. My concern is why , when allowing to create two #table with same table name like that y dont we have same primary key constraint name for that # table. Is that the restriction in sql server 2005 or is it some defect.

What visakh16 said,

Also, your #tables aren't actually created with the same names. it just looks that way.

Try this


IF OBJECT_ID('tempdb..#dump') IS NOT NULL DROP TABLE #dump

CREATE TABLE #dump (
	[foo] VARCHAR(255)
	, [bar] VARCHAR(255)
	)

USE TEMPDB

SELECT
	Table_Catalog
	, Table_Name
FROM
	information_schema.tables
WHERE
	table_name LIKE '%dump%'

#dump in reality is called something like:

#dump_______________________________________________________________________________________________________________00000004C19F

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources