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 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.