Check constraint UDF with multiple input parameters not working

Question

I'm trying to implement a check constraint on a table such that records can't be inserted where there exists a record for which two of the columns ("Int_1" and "Int_2") already have the value we're trying to insert E.g.:

ID     Name     Int_1     Int_2
1      Dave       1         2

Inserting (2, Steve, 2, 2) into the table above would be okay, as would (3, Mike, 1, 3), but inserting values where Int_1 AND Int_2 already exist is not allowed, i.e. (4, Stuart, 1, 2) is illegal.

I thought defining my table thus would work:

CREATE TABLE [Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Int_1] [int] NOT NULL,
    [Int_2] [int] NOT NULL,
    CONSTRAINT [chk_Stuff] CHECK (dbo.chk_Ints(Int_1, Int_2)=1))

where: dbo.chk_Ints is defined:

CREATE FUNCTION [dbo].[chk_Ints](@Int_1 int,@Int_2 int)
RETURNS int
AS
BEGIN

DECLARE @Result int

IF NOT EXISTS (SELECT * FROM [Table] WHERE Int_1 = @Int_1 AND Int_2 = @Int_2)
BEGIN
    SET @Result = 1
END
ELSE 
BEGIN
    SET @Result = 0
END

RETURN @Result
END

GO

When using the combo above, if I try to insert any record whatsoever, SQL tells me I've broken my check constraint. I can remove all rows from the table and try to insert a first record, and SQL tells me I've broken my constraint, which I can't possibly have done!

I've scoured the internet for quite a while now looking for examples of check constraints where the UDF depends on multiple table columns, but to no avail. Any ideas as to why this might not work?

Thanks in advance :)

Answer 1

Yes, this may seem baffling until you realise what's going on, at which point it becomes quite obvious.

The function is called for the values that are in the row you are trying to insert. But think of how the function is being called. It is a check constraint that calls it.

Next, think of the parameters being passed. Where do they come from? According to the definition, the check constraint takes them from columns Int_1 and Int_2.

So, it passes them as column values. But column values must belong to a row. Which row is it in this case? The one you are trying to insert!

That means your row is inserted at this point, only the transaction is still pending. And yet the fact that the row is in the table is crucial, because that's what the function finds and reports on with the 1 result.

Thus, what's happening is this:

• you are trying to insert a row,

• the function sees that row and says that a row with the given parameters already exists,

• the check constraint "reacts" accordingly by prohibiting the insert,

• the insert is rolled back.

Of course, now that you realise all that, it is easy to come up with a different logic of checking for duplicates. Basically, your function should "keep in mind" that the new row is already in the table, and so it should try and determine whether its presence in the table violates any rules that you want to establish. You could, for instance, count the rows matching the given parameters and see if the result is not greater than 1:

IF (SELECT COUNT(*) FROM [Table] WHERE Int_1 = @Int_1 AND Int_2 = @Int_2) < 2
BEGIN
    SET @Result = 1
END
ELSE 
BEGIN
    SET @Result = 0
END

However, the entire idea of using a function in a check constraint for this job is very much inferior to just adding a unique constraint on the two columns, as suggested by @a_horse_with_no_name. Do this:

ALTER TABLE [Table]
ADD CONSTRAINT UQ_Table_Int1_Int2 UNIQUE (Int_1, Int_2);

and you can forget about duplicates.