Enforce referential integrity between two groups of data
Thanks @Joel Brown, your comment pointed me in the direction I needed to go. This is what I ended up with, which satisfies all of my requirements:

CREATE TABLE RuleGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_rulegroups PRIMARY KEY (ID)
)

CREATE TABLE Rules
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    RequiredKeyInSourceData INT not null,
    RequiredValue int null

    CONSTRAINT pk_rules primary key (ID),
    CONSTRAINT fk_rules_groupID_on_rulegroups_ID FOREIGN KEY (GroupID) REFERENCES RuleGroups(ID) ON DELETE CASCADE
)

CREATE TABLE ActionGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_actiongroups PRIMARY KEY (ID)
)

CREATE TABLE Actions
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    SomeColumn INT,
    SomeOtherColumn INT
    -- ... more columns

    CONSTRAINT pk_actions primary key (ID),
    CONSTRAINT fk_actions_groupID_on_actiongroups_ID FOREIGN KEY (GroupID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

CREATE TABLE RuleToActionMappings
(
    RuleGroup_ID INT NOT NULL,
    ActionGroup_ID INT NOT NULL

    CONSTRAINT pk_mappings PRIMARY KEY (RuleGroup_ID, ActionGroup_ID),
    CONSTRAINT uc_mappings_rulegroup_ID UNIQUE (RuleGroup_ID),
    CONSTRAINT uc_mappings_actiongroup_ID UNIQUE (ActionGroup_ID),
    CONSTRAINT fk_rulegroup_ID_on_rulegroups_ID FOREIGN KEY (RuleGroup_ID) REFERENCES RuleGroups(ID) ON DELETE CASCADE,
    CONSTRAINT fk_actiongroup_ID_on_actiongroups_ID FOREIGN KEY (ActionGroup_ID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)


How I'm using it:
Get the rule groups from the DB and match the input data against them:

select Rules.GroupID, rules.RequiredKeyInSourceData, rules.RequiredValue from Rules


Then in the application code:


partition the rules into sets (by GroupID)
match the sets against the input data


stop when a complete set matches or all rulesets have been analyzed
exec a select query to find the corresponding action set





select Actions.* from Actions
join RuleToActionMappings as m on m.ActionGroup_ID = Actions.GroupID
where m.RuleGroup_ID = @MatchedRuleGroupID