PROWAREtech

articles » current » sql » ms-sql » constraints

MS SQL Server: Find and Modify Constraints

Find and modify MS SQL Server 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

This site uses cookies. Cookies are simple text files stored on the user's computer. They are used for adding features and security to this site. Read the privacy policy.
CLOSE