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?
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')