Add FK / Constraint to subsection of table

Setup

Here's two simplified* tables that illustrate the problem I have
_{*The actual tables are built such that I can't really refactor the columns or split them apart easily}

Table: CodeValues

<!-- language: lang-none --> <pre><code>| <b>Code<i>Set</i></b> | <b>Code<i>Value</i></b> | <b>Code<i>Text</i></b> | |---------|-----------|----------| | States | 1 | Vermont | | States | 2 | Hawaii | | YN | 1 | Yes | | YN | 2 | No | </pre></code>

^{Where CodeSet + CodeValue are a composite primary key}

Table: Address

<!-- language: lang-none --> <pre><code>| <b>AddressID</b> | <b>Zip</b> | <b>State</b> | |-----------|-------|-------| | 1 | 96701 | 2 | | 2 | 05001 | 1 | | 3 | 05602 | 1 |</pre></code>

^{Where AddressID is the primary key}

---

What is the appropriate DB constraint to add to Address.State?

It should always be a value that is present in CodeValues Where CodeSet = 'States', but I don't believe I can create a Foreign Key that is part of a Composite Primary Key

Should it just be a check constraint based on a query like this?

CREATE FUNCTION checkCodeValues(
    @codeSet   VARCHAR(50),
    @codeValue SMALLINT

)
RETURNS BIT
AS
BEGIN
    IF EXISTS (SELECT * FROM CodeValues WHERE CodeSet = @codeSet
                                          AND CodeValue = @codeValue)
        RETURN 1
    RETURN 0
END

ALTER TABLE Address
    WITH CHECK ADD CONSTRAINT CK_State
    CHECK (checkCodeValues('States', State))

My concern is that db design tools won't really recognize the full impact of this constraint against, when it really is a FK, but just against a subsection of the table.

One method uses a little bit more storage, but it accomplishes what you want:

create table addresses (
    . . .,
    codeset as (convert(varchar(?), 'states')) persisted,
    foreign key (codeset, state) references codevalues (codeset, codevalue)
);

The ? is for the length of the varchar() column in the codevalues table.

Happily, you don't need triggers or user-defined functions to accomplish this.

Unhappily, you need to persist the codeset in each row, so that eats up a wee bit of space in the table.