2

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.

KyleMit
  • 30,350
  • 66
  • 462
  • 664

1 Answers1

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hey Gordon, so this returns the error "*`Columns participating in a foreign key relationship must be defined with the same length and scale`*" because the `persisted` computed column is a fixed width and doesn't play nice with a variable width code set name field (all codesets names aren't going to have identical lengths). [Here's a fiddle demonstrating the issue](http://www.sqlfiddle.com/#!18/ca9449/1). Any ideas how to work around that? – KyleMit May 01 '19 at 13:28