Using IF EXISTS & NOT EXISTS DROP Primary Key Constraint

Question

Using IF EXISTS & NOT EXISTS DROP Primary Key Constraint

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 14, 2012 at 2:24 pm

#391799

I was trying to add code to Drop the PK, if it exists and after a Load, I want to Create the PK if it does not exist.
Any help would be appreciated.
ALTER TABLE Algorithm_Literals
DROP CONSTRAINT PK_Algorithm_Literals
ALTER TABLE Algorithm_Literals
ADD CONSTRAINT PK_Algorithm_Literals PRIMARY KEY CLUSTERED
(Literal)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

This should be what you are looking for. First one checks if the object exists in the sys.objects "Table" and then drops it if true, the second checks if it does not exist and then creates it if true.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PK_MyPK') AND type in (N'U'))

ALTER TABLE MyTable DROP CONSTRAINT PK_MyPK

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PK_MyPK') AND type in (N'U'))

ALTER TABLE MyTable

ADD CONSTRAINT PK_MyPK

PRIMARY KEY CLUSTERED (Literal)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

capn.hector (3/14/2012)
This should be what you are looking for. First one checks if the object exists in the sys.objects "Table" and then drops it if true, the second checks if it does not exist and then creates it if true.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PK_MyPK') AND type in (N'U'))
ALTER TABLE MyTable DROP CONSTRAINT PK_MyPK
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PK_MyPK') AND type in (N'U'))
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyPK
PRIMARY KEY CLUSTERED (Literal)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

The IF NOT EXISTS does not work. The select statement does not return anything and when it executes I get an error that the table already has an error on it.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Does this not work?

IF NOT EXISTS (

SELECT * FROM sys.key_constraints

WHERE type = 'PK'

AND OBJECT_NAME(parent_object_id) = 'Algorithm_Literals'

)...

John

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

John Mitchell-245523 (3/15/2012)
Does this not work?
IF NOT EXISTS (
SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = 'Algorithm_Literals'
)...
John

The problem is that I have primary keys for tables with the same name (will have once I drop & recreate) in different schemas.

So there may be an issue with the NOT EXISTS as well. Need to test.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I should have mentioned that I have the Algorithm_Literals

in two schemas 'cyp' and 'ctl'.

I regret the omission.:blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Then filter on the schema_id in sys.key_constraints as well as the name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

You can check for schema name as well:

SELECT * FROM sys.key_constraints

WHERE type = 'PK'

AND OBJECT_NAME(parent_object_id) = 'TableName'

AND SCHEMA_NAME([schema_id]) = 'SchemaName'

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]