Having data from another table put in into check constraint

Question

I have table say table1 with a colum say checkColumn,

now I want to put a check constraint to the field checkColumn which would allow only data which is there in another table say table 2 for some conditions,

I tried this like this,

ALTER TABLE table1
ADD CHECK (checkColumn=(select field1 from table2 where field2='ABC') ) //the select is not scalar

but as I realized it doesn't allow sub-query string in the check condition,I searched a little and read I should use a foreign key,or trigger, or something else,but didn't really understand how to implement those examples here,so posting this as a separate question.

Answer 1

Unfortunately you can not insert Sub Query into context of Check constraint. But here I would like give suggestion, You can use any trigger or function , You can use foreign key constraint to check data dependency I would like to share one example with function. e.g.

CREATE FUNCTION fn_Check_Rollnumber (
    @Rollnumber INT
)
RETURNS VARCHAR(10)
AS
BEGIN
    IF EXISTS (SELECT Rollnumber FROM Table_Student WHERE Rollnumber = @Rollnumber)
        return 'True'
    return 'False'
END

Now you can use this function in you Check context like,

ALTER TABLE Table_Fees 
    WITH CHECK ADD CONSTRAINT CK_RollCheck
    CHECK (fn_Check_Rollnumber(Rollnumber) = 'True')