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;