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:

CREATE table sady
(
   customer varchar(50),
   phoneNo varchar (50),
   status bit
)

Now we are creating a function that will check whether a customer has any other number in active status.

ALTER function tempfunc(@customer varchar(50))
returns bit
as
begin
declare @rt bit, @cnt int
set @cnt =(select COUNT(*) FROM sady where customer= @customer AND status =1)
if isnull(@cnt,0)>1
SET @rt= 0
else
SET @rt= 1
RETURN @rt

end

Now we are adding the constraint to the table.

ALTER table sady add constraint ck_temp
check (dbo.tempfunc(customer)=1)

Now let's insert some records into the table.

INSERT INTO sady VALUES ('Harieswaran','9600914261','0')
INSERT INTO sady VALUES ('Harieswaran','9600914261','0')
INSERT INTO sady VALUES ('Harieswaran','9600914262','0')
INSERT INTO sady VALUES ('Harieswaran','9600914263','0')

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.



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:



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.

UPDATE sady SET status=1

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





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

Alternatively we are forced to use triggers.