Drop Check Constraint in SQL Server Database Table
To drop SQL Check Constraint where constraint's name is unknown, sys.check_constraints system view can be used. This SQL Server tutorial show how developers can create sql procedure that drop check constraint with only table name and column name parameters.
SQL Server Check Constraint is used by database administrators and T-SQL programmers to apply a validation check defined at check constraint expression on inserted or updated column data.
Let's continue our talk by building sample case for the sake of sql tutorial. Here is the Create script of a table which has a column where a SQL Check Constraint is defined. The sql check constraint validates the entered data for CustomerGroup column is one of the values in the given list.
CREATE TABLE Customer
(
CustomerId int identity(1,1) not null,
Title nvarchar(1000) not null,
SectorId smallint,
CustomerGroup char(2) check ( CustomerGroup IN ('AA','BB','EE') ),
-- more columns ....
)
Now we can test how SQL constraint works with Insert statements.
Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz SQL','AA')
Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz SAP',NULL)
Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz WP8','WP')
Although the CustomGroup column insert value in second SQL Insert statement is NULL, the first two INSERT INTO statements execute successfully. NULL case does not cause SQL Server Check Constraint to throw exception in SQL statements. On the other hand, last SQL Insert Into statement with column value does not validate SQL check constraint defined on that table column. When the SQL check constraint is not validated the following error is thrown by SQL Server engine.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__Customer__Custom__71D1E811". The conflict occurred in database "KodyazSQL2012DB", table "dbo.Customer", column 'CustomerGroup'.
The statement has been terminated.
On this SQL Server exception message, we can learn that the SQL Check constraint name is CK__Customer__Custom__71D1E811. Error detail gives the database name, as well as the table and the column name where the SQL check constraint is defined.
If SQL programmer has to drop SQL constraint (check constraint) on table column, if the name of the constraint is know following syntax can be used.
ALTER TABLE TableName DROP Constraint ConstraintName
-- for example
ALTER TABLE Customer DROP Constraint CK__Customer__Custom__71D1E811
After you drop SQL Check Constraint, it is now possible to insert data into database table that caused error before in this SQL tutorial.
Of course life is not always as easy for database developers or SQL Server administrators. If you don't know the name of the SQL Server check constraint, SQL professionals should be able to drop constraint programmatically.
Here is where SQL Server sys.check_constraints system view comes first for help.
sys.check_constraints SQL view returns the list of check constraints defined on that database table columns.
Let's execute a SELECT query on sys.check_constraints and see what does it display.
SELECT * FROM sys.check_constraints
As you see in following screenshot, check_constraints system view has the name schema_id, parent_object_id, parent_column_id and name columns.
SQL Server check constraints system view sys.check_constraints contains all data required to drop and re-create an SQL constraint
Transact-SQL programmers can create a stored procedure to find the name of the check constraint using the table name and the column name. Then the constraint name can be used in ALTER Table DROP Constraint command syntax.
Here is a stored procedure which drop check constraint that the sql constraint name is unknown but the table and column name is known.
ALTER PROC SP_DROP_Check_Constraint (
@table_name sysname,
@column_name sysname
)
AS
declare @check_constraint_name sysname, @sql nvarchar(max)
select @check_constraint_name = name from sys.check_constraints
where
parent_object_id = OBJECT_ID(@table_name) -- Table name
AND type = 'C' -- Check Constraint
AND parent_column_id = (
select column_id from sys.columns
where
object_id = OBJECT_ID(@table_name)
and name = @column_name -- Column name
)
if @check_constraint_name is not null
begin
SET @sql = N'ALTER TABLE ' + @table_name + ' DROP Constraint ' + @check_constraint_name
-- print @sql
EXEC sp_executesql @sql
end
else
throw 50001, 'SQL Server Check Constraint does not exist', 1
Now developers can try above SQL stored procedure to drop a check constraint whose name is defined in code using the table and column names. Let's see the Drop Check Constraint sql procedure in action.
SP_DROP_Check_Constraint 'Employee', 'DateofEntry'
If there is no such SQL Server check constraint, stored procedure will throw error shown below. You can test it running the stored procedure once more after dropping SQL constraint.
Msg 50001, Level 16, State 1, Procedure SP_DROP_Check_Constraint, Line 27
SQL Server Check Constraint does not exist
Developers will find a similar tutorial showing how to drop Default Constraint on a Table Column without knowing related constraint name.