MS SQL Server: Find and Modify Constraints

Use the following SQL Server statement to find out which constraints are defined in the database.

SELECT	*
FROM		INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Or use this one to find out the constraints on a single table (authors in this case):

SELECT	OBJECT_NAME(constid) 'Constraint Name',
			constid 'Constraint ID',
			CASE (status & 0xF)
			WHEN 1 THEN 'Primary Key'
			WHEN 2 THEN 'Unique'
			WHEN 3 THEN 'Foreign Key'
			WHEN 4 THEN 'Check'
			WHEN 5 THEN 'Default'
			ELSE 'Undefined'
			END 'Constraint Type',
			CASE (status & 0x30)
			WHEN 0x10 THEN 'Column'
			WHEN 0x20 THEN 'Table'
			ELSE 'NA'
			END 'Level'
FROM		sysconstraints
WHERE	 id=OBJECT_ID('authors')

To create a unique constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1_name,column2_name,column3_name,...)

To create a primary key constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1_name,column2_name,column3_name,...)

To delete a constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

---