Foreign key, key split in two tables I have to record a list of events made of action, reason (optional, may be multiple), user.
I have a master table with all the valid action, and another with all the valid action-reason combination.
All tables have a unique identity (not displayed in the example).

master_actions
action
A1
A2
A3

master_action_reasons
action  reason
A1      NULL
A2      R1
A2      R2
A3      R1
A3      R2
A3      R3

events
action  user   id
A1      John   E1
A3      Lisa   E2
A3      Sue    E3

reasons
event reason
E2    R1 
E2    R3
E3    R2


Question: how do I make sure that the combination action-reason is valid, i.e. exists in master_action_reasons? Since the action is in one table, and the reason in another, I cannot use a foreign key.
This is a good case for CHECK constraints using a function. Step one, write a function that does something like (EDIT: Used Event instead of Action sorry!):

CREATE FUNCTION CheckReasonValid(@intReason, @intEvent)
RETURNS bit
AS 
BEGIN
   DECLARE @NumRows int
   SELECT @NumRows = COUNT(*) FROM master_action_reason mar 
       INNER JOIN Events e ON mar.Action = e.Action
       WHERE mar.Reason = @intReason AND e.Event = @Event
   RETURN @NumRows 
END;


Then in your table definition, add the CHECK constraint, something like:

ALTER TABLE Reasons
ADD CONSTRAINT chkActionReasonValid CHECK (dbo.CheckReasonValid(Reason,Event) &gt;= 1 );