SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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 ....
)
Code

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')
Code

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
Code

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
Code

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
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
Code

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'
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.