TSQL: Unique Constraint

To add a UNIQUE constraint to a column in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE({UniqueColumnName})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentName UNIQUE (NAME)

To add a UNIQUE constraint to two columns in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE
({UniqueColumnName1},{UniqueColumnName2})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentNameRoom
UNIQUE(NAME,RoomNumber)

TSQL Script to drop all constraints on a given table

A SELECT statement that will give you the 'drop constraint' queries for all constraints related to MyTable

SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' DROP CONSTRAINT ' + f.name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'MyTable'
OR OBJECT_NAME (f.referenced_object_id) = 'MyTable'

A SELECT statement that will give you the 'add constraint' queries for all constraints related to MyTable

SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' WITH CHECK ADD CONSTRAINT ' +
f.name + ' FOREIGN KEY(' + COL_NAME(fc.parent_object_id, fc.parent_column_id ) + ') REFERENCES ' + OBJECT_NAME(f.referenced_object_id) + ' (' + COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) + ');'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'MyTable'
OR OBJECT_NAME (f.referenced_object_id) = 'MyTable'