EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at firstname.lastname@example.org.
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:
(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.