Post
Ask Question

Error With Check Constraint in SQL Server

I was really surprised to learn that using a User Defined Function (UDF) when adding Check Constraints to a table column will not work the way that we are expecting.

Consider the following scenario

We have a customer, phone no and status column. A customer can have multiple phone numbers but only one phone number should be active for a customer.

So now let's try to do that with a check constraint.

Let's create a table named sady as in the following:

  1. CREATE table sady  
  2. (  
  3.    customer varchar(50),  
  4.    phoneNo varchar (50),  
  5.    status bit  
  6. )  
Now we are creating a function that will check whether a customer has any other number in active status.
  1. ALTER function tempfunc(@customer varchar(50))  
  2. returns bit  
  3. as  
  4. begin  
  5. declare @rt bit, @cnt int  
  6. set @cnt =(select COUNT(*) FROM sady where customer= @customer AND status =1)  
  7. if isnull(@cnt,0)>1  
  8. SET @rt= 0  
  9. else   
  10. SET @rt= 1  
  11. RETURN @rt  
  12.   
  13. end  
Now we are adding the constraint to the table.
  1. ALTER table sady add constraint ck_temp  
  2. check (dbo.tempfunc(customer)=1)  
Now let's insert some records into the table.
  1. INSERT INTO sady VALUES ('Harieswaran','9600914261','0')  
  2. INSERT INTO sady VALUES ('Harieswaran','9600914261','0')  
  3. INSERT INTO sady VALUES ('Harieswaran','9600914262','0')  
  4. INSERT INTO sady VALUES ('Harieswaran','9600914263','0')  
status

The preceding records are inserted successfully into the table.

Now let's try inserting another phone number to the same customer in active status.

INSERT INTO sady VALUES ('Harieswaran','9600914264','1')

Since there is no active number associated with the customer the preceding statement is executed successfully and the record is inserted.

show result

Now let's try to insert another record for the same customer with active status.

INSERT INTO sady VALUES ('Harieswaran','9600914265','1')

We get the following error:

error

Now here comes the turning point

If we try to update the record for the inserted customer to active status, then the Check Constraint is not checked correctly.
  1. UPDATE sady SET status=1  
result

Note: We have enforced the check constraint for both update and insert statement.

table design

expression

So there is no way of achieving the preceding task by using check constraint.

Alternatively we are forced to use triggers.


Similar Articles