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

| CodeSet | CodeValue | CodeText |
|---------|-----------|----------|
| States  | 1         | Vermont  |
| States  | 2         | Hawaii   |
| YN      | 1         | Yes      |
| YN      | 2         | No       |

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

Table: Address

| AddressID | Zip   | State |
|-----------|-------|-------|
| 1         | 96701 | 2     |
| 2         | 05001 | 1     |
| 3         | 05602 | 1     |

^{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.

Answer

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.