EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at questions@tsqlsolutions.com.

Download the Code iconUsing 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 1                              INSERT 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.