Using If Not Exists on Primary Key

user_8830634 0
Viewed 10894

I have an existing table in my sql server and I want to add a primary key clustered on a column of that table. I know the syntax would be :

ALTER TABLE PromotionBenefit  
ADD CONSTRAINT PK_PromotionBenefit2 PRIMARY KEY CLUSTERED (PromotionBenefitCode);  
GO

The problem is that column already has a primary key (same constraint name) on it. So it throws an error. That is fine. I would like to know if it's possible to add an IF NOT Exists on my query so it wouldn't throw any error.

What would be the syntax to use?

EDIT: Is there a way of using this way: IF NOT EXISTS.... CREATE PRIMARY KEY CLUSTERED instead of altering table like shown above? Can it be done like this way :

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PromotionBenefit]') AND name = N'idx_EventCode')
CREATE NONCLUSTERED INDEX [idx_EventCode] ON [dbo].[PromotionBenefit]
(
    [EventCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ssms sql-server sql
2 Answers

You have to do the following:

DECLARE @IsPrimary INT

SELECT @IsPrimary=COUNT(1)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'PromotionBenefit'

IF @IsPrimary>0
BEGIN
    SELECT 'Table already have Primary Key'
END
ELSE
BEGIN
    ALTER TABLE PromotionBenefit  
    ADD CONSTRAINT PK_PromotionBenefit2 PRIMARY KEY CLUSTERED (PromotionBenefitCode);  
    GO
END

If you think, it's not better then Try this:

IF NOT EXISTS(
SELECT 1
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'PromotionBenefit')
BEGIN
    ALTER TABLE PromotionBenefit  
    ADD CONSTRAINT PK_PromotionBenefit2 PRIMARY KEY CLUSTERED (PromotionBenefitCode);  
    GO
END

Try this:

IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type = 'PK' AND  parent_object_id = OBJECT_ID ('PromotionBenefit'))
BEGIN
    ALTER TABLE PromotionBenefit  
    ADD CONSTRAINT PK_PromotionBenefit2 PRIMARY KEY CLUSTERED (PromotionBenefitCode)
END

Hope it helps you.

user_38765650

One shorter way to do what expected :

IF OBJECT_ID('idx_EventCode') is null
   CREATE NONCLUSTERED INDEX [idx_EventCode] ON [dbo].[PromotionBenefit] 
   ( [EventCode] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
     DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
     FILLFACTOR = 80) ON [PRIMARY]
GO

OBJECT_ID works with any object from SqlServer, I also use it to detect the existence of tables, views, stored procedures ...

user_43263130
Related