11

I have the following:

IF OBJECT_ID(N'[dbo].[webpages_Roles_UserProfiles_Target]', 'xxxxx') IS NOT NULL
   DROP CONSTRAINT [dbo].[webpages_Roles_UserProfiles_Target]

I want to be able to check if there is a constraint existing before I drop it. I use the code above with a type of 'U' for tables.

How could I modify the code above (change the xxxx) to make it check for the existence of the constraint ?

3 Answers3

27
 SELECT
    * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

or else try this 

  SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

or

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
 BEGIN 
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME 
END
Gayathri L
  • 1,407
  • 11
  • 13
  • If the table have PK, Do something like this IF EXISTS(SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID('Tablename')) BEGIN ALTER TABLE Tablename DROP CONSTRAINT CONSTRAINTNAME END – Gayathri L Jul 06 '13 at 11:10
9

Try something like this

IF OBJECTPROPERTY(OBJECT_ID('constraint_name'), 'IsConstraint') = 1
    ALTER TABLE table_name DROP CONSTRAINT constraint_name
wqw
  • 11,771
  • 1
  • 33
  • 41
  • IF OBJECT_ID('Schema.keyname') IS NULL ALTER TABLE Schema.tablename ADD CONSTRAINT keyname... – Martin B Feb 23 '18 at 15:35
  • 1
    @MartinB Yes, this looks like a nice shortcut for `ADD CONSTRAINT`! For `DROP CONSTRAINT` skipping `IsConstraint` check risks `ALTER TABLE` failure as the `OBJECT_ID` will return non-null if 'Schema.keyname' is an index or a table. – wqw Feb 25 '18 at 11:43
4

you can do something like : 

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

BEGIN
....
END
ELSE
Hiren Dhaduk
  • 2,760
  • 2
  • 19
  • 21