Using If Not Exists on Primary Key
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.