Dropping Constraints conditionally

dpatfield66

Well-known member
Joined
Apr 6, 2006
Messages
136
Programming Experience
5-10
I have 2 scripts that produced a default constraint with different names for the same field.

Ex: Script 1 added [DF_OBAdmit_Field1]
Script 2 added [Field1]

Now I want an upgrade script that drops this constraint, but I want the command to be conditional:

IF (CONSTRAINT NAME = DF_OBAdmit_Field1)
ALTER TABLE DROP CONSTRAINT DF_OBAdmit_Field1
ELSE
ALTER TABLE DROP CONSTRAINT Field1
=========================================

This code does not work, obviously. I also tried IF EXISTS CONSTRAINT, etc.. and that did not work.

Anyone know what I need to type here?
 
Haven't tested this out but it should be pretty close.

VB.NET:
IF EXISTS (SELECT * 
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
	WHERE CONSTRAINT_SCHEMA='dbo' 
		AND CONSTRAINT_NAME='DF_OBAdmin_Field1'
		AND TABLE_NAME='Your Table')

BEGIN
	ALTER TABLE TableName
	DROP CONSTRAINT [DF_OBAdmin_Field1]
END

ELSE
	IF EXISTS (SELECT *
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		WHERE CONSTRAINT_SCHEMA='dbo'
			AND CONSTRAINT_NAME='Field1'
			AND TABLE_NAME='Your Table')

BEGIN
	ALTER TABLE TableName
	DROP CONSTRAINT [Field1]
END
 
Back
Top