using a check constraint to enforce values in a column based on values in another table

You can enforce business rules in a column based on values in another column thats in another table by creating a check constraint with a UDF function.

On scenario that you may need to do this is imagine you have a global table that holds states. this table holds stateIds for all entities in your database (for instance customer stateIds, order stateIds, sale stateIds...etc)

In this case you may not be able to use a foriegn key constraint ( because you need to restrict a subset of the stateIds per table)

Here is an exanple on how you can do tha.


Assume your global table that holds stateIds is called State

CREATE TABLE t1 (stateId INT NOT NULL)

go

CREATE FUNCTION dbo.fnc_IsValidState(@StateId INT, @TypeId Int) RETURNS BIT
AS
BEGIN

DECLARE @flag BIT = 1

IF NOT EXISTS (SELECT 1 FROM state WHERE stateId =@StateId AND ClassId=@TypeId)
BEGIN

SET @flag = 0

END

RETURN @flag
END



go


ALTER TABLE dbo.[t1] WITH NOCHECK ADD CONSTRAINT [CK_StateCHeck] CHECK ((dbo.fnc_IsValidState(StateId,1)=1))