Sub queries in check constraint

Question

I have table designed in SQL-Server 2008 R2.

I have a column in that table which needs to be checked against another table when data is inserting.

ALTER TABLE Table1         WITH CHECK ADD CONSTRAINT CK_Code         CHECK (MyField in (Select Field From Table2)) 

This cause an error

Sub-queries are not allowed in this context. Only scalar expressions are allowed.

I have looked at this question about Check Constraint - Subqueries are not allowed in this context.

Is there any way of achieving this without using a trigger?

Answer 1

Note, what you really want is a foreign key constraint. That said, to get a "query" into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (     @field DATATYPE(?) ) RETURNS VARCHAR(5) AS BEGIN     IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)         return 'True'     return 'False' END 

Something like that. Not tested.

Then you can add it to your check like so

ALTER TABLE Table1     WITH CHECK ADD CONSTRAINT CK_Code     CHECK (myFunction(MYFIELD) = 'True')