You can use a UDF function in your check constraint to check the values in another table.
here is an example;
assume you have a table called state which have all possible stateIds you use in many tables (so it is not possible to use a foriegn key constraint)
-- we want to check if the values in the following tables are a subset of the main state table
CREATE TABLE t1 (stateId INT NOT NULL)
-- create a function to check the state values and return 1 if stateId is invalid
CREATE FUNCTION dbo.fnc_IsValidState(@StateId INT, @TypeId Int) RETURNS BIT
DECLARE @flag BIT = 1
IF NOT EXISTS (SELECT 1 FROM state WHERE stateId =@StateId AND TypeId=@TypeId)
SET @flag = 0
--Add a check constraints to the table to call the function
ALTER TABLE dbo.[t1] WITH NOCHECK ADD CONSTRAINT [CK_StateCHeck] CHECK ((dbo.fnc_IsValidState(StateId,1)=1))
-- test by adding some invalid values
INSERT INTO t1 VALUES(12)
I personally prefer FK constraints if possible, this code is to show that it is possible to do what you are asking for using a function, but it may not be the best solution..
hope you find it useful