Alter a Constraint In SQL Server

In this blog, you will learn how to alter a constraint in SQL Server.

One of the most common interview questions in SQL Server is how to alter a constraint.

 

Answer

 

The answer to this question is that we can't alter any constraint. If you want to alter a constraint, then first drop that constraint and recreate a new constraint.

 

The script is given below to create and drop the constraints.

 

Create a constraint

 

Alter table table_name

Add constraint constraint_name  constraint_definitation.

 

Drop a constraint

Alter table table_name

Drop constraint constraint_name

 

Drop all constraints of a table

DECLARE @sqlQuery NVARCHAR(MAX) = N '';
SELECT @sqlQuery += N '
ALTER TABLE ' + TABLE_NAME +
' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table_name';
EXEC sp_executesql @sqlQuery;