SQL T-SQL Temp Tables

The below code works, but when it runs a 2nd time, the error message is generated:
"There is already an object named 'PK_UpdatesQQ' in the database."

I cannot get the constraint to drop even though the temp table is dropped.  There appears to be a unique step because it is a constraint attached to a temporary table?


CREATE TABLE [dbo].[#Temp00](
[VisitID] [int] NOT NULL,
DecisionTmForDispFromObsv varchar(24) NOT NULL DEFAULT '',
DispositionType varchar(300) NOT NULL DEFAULT '',
DischargeTo varchar(300) NOT NULL DEFAULT ''
) ON [PRIMARY]

ALTER TABLE [dbo].[#Temp00] ADD  CONSTRAINT [PK_UpdatesQQ] PRIMARY KEY CLUSTERED ( [VisitID] ASC ) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
dastaubAsked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
Try to delete the PK Constraint before dropping the table:
IF EXISTS (SELECT * FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = OBJECT_ID(N'[dbo].[#Temp00]') AND name = N'PK_UpdatesQQ')
BEGIN
    ALTER TABLE [dbo].[#Temp00] DROP CONSTRAINT [PK_UpdatesQQ]
END

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.[#Temp00]'))
 BEGIN 
ALTER TABLE [#Temp00] DROP CONSTRAINT [PK_UpdatesQQ]
END

Open in new window

0
 
dastaubAuthor Commented:
The answer is to create a constraint without a name.  Example below.

CREATE TABLE [dbo].[#Temp00](
[VisitID] [int] NOT NULL,
DecisionTmForDispFromObsv varchar(24) NOT NULL DEFAULT '',
DispositionType varchar(300) NOT NULL DEFAULT '',
DischargeTo varchar(300) NOT NULL DEFAULT ''
PRIMARY KEY CLUSTERED ( [VisitID] ASC ) WITH (IGNORE_DUP_KEY = ON))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.