Using a UDF in a CHECK Constraint to Validate a Column

Itzik Ben-Gan shows readers how to circumvent SQL Server's lack of support for using subqueries in CHECK constraints.

Itzik Ben-Gan

October 31, 2001

3 Min Read
ITPro Today logo

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:


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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like