Recently i caught in a peculiar situation where i need to modify the check constraint expression to include more criteria to be validated through T-SQL. Although there is no declarative command or mechnism to modify the expression except dropping and recreating the check constraint. One exception is to use the Sql Management studio to modify it in design mode.
But modifying using design mode was out of scope. The actual work was to alter or modify the defintion of a check constraint created with an implicit name that is given by sql server itself when one is not provided by the user.
Lets first create a test table that contains 2 columns first is Name and another is Profile. Profile column is used to hold if user is a Administrator 'A' or Operator 'O'.
Create table Test(
[Name] nvarchar(10),
[Profile] char(1) Check([Profile] = 'A' OR [Profile] = 'O')
)
Insert Into Test Values('User1','A')
Insert Into Test Values('User2','O')
Select * from Test
A new table is created and two rows are created in the table. One thing is to be given an attention is that the check constraint is not given a name. So Sql server will do the job and give a name in the format something like "CK__Test__Profile__XXXXXXXX'.
As the application evolves, suppose a new profile is required for say Guest. I take the initial 'G' that will denote the Guest. I write an insert statement to create a new row in Test table with the guest profile as follows
Insert Into Test Values('User3','G')
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__Test__Profile__59063A47". The conflict occurred in database "db_gei_test", table "dbo.Test", column 'Profile'.
The statement has been terminated.
It throws the error that says value conflicted with the check constraint. Most obvious solution that comes to mind is to extend the expression but for that we need to have a name to identify the check constaint on the column.
An alternative solution could be to, infact solution i need, is to write a T-SQL to first find the name of the check constraint defined on the Table Test and column [Profile]. Second step is to alter table and recreate the check constraint with the extended expression. Here we go
declare @constraintName nvarchar(100)
select @constraintName = OBJECT_NAME(scc.OBJECT_ID)
from sys.check_constraints scc
Inner Join sys.columns sc on scc.parent_object_id = sc.object_id and scc.parent_column_id = sc.column_id
where OBJECT_NAME(scc.parent_object_id) = 'Test' and sc.[name] = 'Profile'
and type_desc = 'CHECK_CONSTRAINT'
exec('Alter table Test Drop Constraint ' + @constraintName);
ALTER TABLE [dbo].[Test] ADD Constraint [CK_Test_Profile]
CHECK([Profile] = 'A' OR [Profile] = 'O' OR [Profile] = 'G')
Now run the insert command for creating a user with guest profile.
Insert Into Test Values('User3','G')
Command runs successfully and creates a new row in the Test table, it could be verified by firing a select command on the table.
Hope this tip could make your task of modifing a check constraint easier.