Using a UDF in a CHECK Constraint to Validate a Column

EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at [email protected].

Using a UDF in a CHECK Constraint to Validate a Column

I want to validate a column in table S by allowing only primary key values from two other tables, called P1 and P2. Can I use a CHECK constraint to validate the column? I tried this approach, but SQL Server wouldn't let me use a subquery in the CHECK constraint to access another table. Here's the code I wrote:

CHECK  (key_col1 IN (SELECT key_col from P1) OR  key_col1 IN (SELECT key_col from P2))

What am I doing wrong?

The ANSI SQL standard lets you use subqueries in CHECK constraints, but SQL Server doesn't support this functionality. However, if you're using SQL Server 2000, you can write a user-defined function (UDF) that performs an existence check against both tables and returns 1 if a row exists in either table and 0 if no row exists. You can then use the UDF in a CHECK constraint to achieve the results you're looking for.

To test this solution, run the code that Listing 1 shows to create sample tables P1, P2, and S. Then, create the function dbo.fn_check_p1p2(), which Listing 2 shows. This function performs the existence check for a key that the function accepts as an argument. You can now add the following CHECK constraint, which invokes the dbo.fn_check_p1p2() function for table S; note that the function takes the key_col1 column as an argument:

ALTER TABLE S  ADD CONSTRAINT CHK_S_key_col1_in_P1P2    CHECK(dbo.fn_check_p1p2(key_col1) = 1)Populate tables P1 and P2 with sample data:INSERT INTO P1(key_col, data_col) VALUES(1, 'a')INSERT INTO P1(key_col, data_col) VALUES(3, 'c')INSERT INTO P2(key_col, data_col) VALUES(2, 'b')INSERT INTO P2(key_col, data_col) VALUES(4, 'd')

Then, try to insert into table S rows with values in key_col1 that exist in either table P1 or P2. The insertion doesn't generate errors.

INSERT INTO S(key_col1, key_col2, data_col) VALUES(1, 1, 'e')INSERT INTO S(key_col1, key_col2, data_col) VALUES(2, 1, 'f')INSERT INTO S(key_col1, key_col2, data_col) VALUES(3, 1, 'g')INSERT INTO S(key_col1, key_col2, data_col) VALUES(4, 1, 'h')

Now, try to insert a row with a key_col1 value that exists in neither table P1 nor P2:

INSERT INTO S VALUES(5, 1, 'i')

This insertion attempt generates the following CHECK constraint violation error:

Server: Msg 547, Level 16, State 1, Line 1INSERT statement conflicted with COLUMN CHECK constraint 'CHK_S_key_col1_in_P1P2'. The conflict occurred in database 'testdb',table 'S', column 'key_col1'.The statement has been terminated.

If you're using SQL Server 7.0, which doesn't support UDFs, you can't implement this solution. Instead, you can write a trigger that determines whether rows inserted or updated in table S have related rows in tables P1 or P2, as Listing 3 shows. You can simply write the trigger to support multirow inserts and updates and perform a nested existence check. If the rows inserted and updated in table S don't have related rows in tables P1 or P2, the trigger rolls back the transaction that caused it to fire and generates an error message.