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
  1. DECLARE @sqlQuery NVARCHAR(MAX) = N '';
  2. SELECT @sqlQuery += N '
  3. ALTER TABLE ' + TABLE_NAME +
  4. ' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
  5. from
  6. INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
  7. WHERE TABLE_SCHEMA = 'dbo'
  8. AND TABLE_NAME = 'table_name';
  9. EXEC sp_executesql @sqlQuery;