Solved

Check if table exist before creating a constraint.

Posted on 2007-03-29
5
473 Views
Last Modified: 2012-06-27
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]

Check if table exist before creating a constraint.

There is already an object named 'PK_Tmp_LN_Discount' in the database.
0
Comment
Question by:mathieu_cupryk
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18819057
if exists (select name from sysobjects where name like '#tmp_ln_discount%')
begin
     drop table #tmp+ln_discount
end
create table...
add constraint...

0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18819082
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Tmp_LN_Discount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]
GO
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18819100
You might not want to drop the table first. It may contain data.

This is more correct. My cut and past went crazy:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Tmp_LN_Discount]')
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]
GO
0
 

Author Comment

by:mathieu_cupryk
ID: 18819164
rboy
what about CREATE TABLE #Tmp_LN_Discount (STR_ID INT NOT NULL, RGST_ID INT NOT NULL, TRAN_ID INT NOT NULL, TRAN_LN_NUM SMALLINT NOT NULL, DISC_CD INT NOT NULL, DISC_AMT MONEY NULL)
      
There is already an object named '#Tmp_LN_Discount' in the database.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18819224
I was looking only at the contraint portion of the command. You can do the same type of query:

if exists (select name from sysobjects where name like '#tmp_ln_discount%')
begin
     drop table #tmp+ln_discount
end

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now