PROWAREtech
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
![](/img/wait2.gif)
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
Comment![](/img/comment-white.svg)