Check to see if a constraint exists in MSSQL.

The basic pattern for the check looks like this...

IF((SELECT COUNT(*) FROM Whatever) > 0)
   BEGIN
      PRINT 'It exists.'
   END
ELSE
   BEGIN
      PRINT 'It does not exist.'
   END

 
 

The select statement will vary from case to case however. Here is what is needed for four circumstances:

1. foreign key constraint:
   SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_Foo'
    
2. primary key constraint:
   SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'PK_Foo'
    
3. unique constraint:
   SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'UQ_Foo'
    
4. default constraint:
   SELECT COUNT(*) FROM sys.columns AS c JOIN sys.default_constraints AS d ON c.column_id = d.parent_column_id WHERE d.name = 'DF_Foo'
    

The unique constraint is approached just like a primary key constraint, these are restraints on varchar columns and the like to make sure that no two items have the same name if names need to be unique. Default constraints are "special cases" for column defaults. This example, for example, shows auto-populating a Guid for a column that uses a Guid as an ID. If you look at the Object Explorer in SSMS and you look at a table in the Databases, the Constraints "folder" below the table will hold the default constraints and the other three types here will be kept in the Keys "folder." All four of the the constraints above may be renamed like this:

EXEC sp_rename 'this', 'that'