Can referential integrity be enforced using alter table?

Question

Is there a way to enforce referential integrity without foreign keys? Is there a way to achieve what I am trying to do below with alter table statement?

ALTER TABLE no.Man  
WITH CHECK ADD  CONSTRAINT chk_Son_Weight CHECK
    (Son_Weight IN (Select distinct Weight from no.Man))
GO

I got the below error by using the code above

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Answer 1

I'm not sure I understand why you think this is better than foreign keys, but yes, you can implement referential integrity in other (inferior) ways. These will be slower than doing it right and fixing the design.

1. Check constraint + UDF

    CREATE FUNCTION dbo.IsItAValidWeight(@Son_Weight int)
    RETURNS bit
    WITH SCHEMABINDING
    AS
    BEGIN
      RETURN 
      (
         SELECT CASE WHEN EXISTS 
         (
           SELECT 1
           FROM no.Man WHERE Weight = @Son_Weight
         ) THEN 1 ELSE 0 END
      );
    END
    GO
   
    ALTER TABLE no.Man WITH CHECK 
      ADD CONSTRAINT chk_Son_Weight 
      CHECK dbo.IsItAValidWeight(Son_Weight) = 1;
   

2. Trigger

   Need to know a lot more about the schema I think, but you can research.