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.



## 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`



<pre><code>| CodeSet | CodeValue | CodeText |
|---------|-----------|----------|
| States | 1 | Vermont |
| States | 2 | Hawaii |
| YN | 1 | Yes |
| YN | 2 | No |
</pre></code>

Where `CodeSet` + `CodeValue` are a composite primary key

### Table: `Address`



<pre><code>| AddressID | Zip | State |
|-----------|-------|-------|
| 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][1]

Should it just be a [check constraint based on a query][2] like this?

```sql
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
```

```sql
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.

[1]: https://stackoverflow.com/q/12679312/1366033
[2]: https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint