Sql-server – Foreign Key with a Constant

constraintdatabase-designforeign keysql serversql server 2014

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB).

Next, I have a second table, but this time it's restricted to entries in table A that have ThingB = 3. The primary key is ThingA, because ThingB is a constant of 3.

Initially, I had thought I could simply:

FOREIGN KEY (ThingA, 3) REFERENCES A(ThingA, ThingB)

But I've learned that's not the case, and I have to create a column for the ThingB:

ThingB INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)

Then,

FOREIGN KEY (ThingA, ThingB) REFERENCES A (ThingA, ThingB)

Is there an alternative to this that doesn't require an extra column, or the DEFAULT + CHECK? One alternative is a persisted, computed column, but I hate that idea too as it's basically a cheat and still adds a new column with physical storage. While on it's own, the INT won't be big, there are several million rows that need it across several tables, and I'd rather not maintain the extra columns.

Here's sample DDL to illustrate the situation:

CREATE TABLE Test1
(
    ThingA INT NOT NULL,
    ThingB INT NOT NULL,
    PRIMARY KEY (ThingA, ThingB)
);

CREATE TABLE Test2
(
    ThingAVal INT NOT NULL,
    ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
    Val INT NOT NULL,
    FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
);

And I've created a db<>fiddle that demonstrates my (current) solution:

If the answer is "No", I'll accept it, but I'm curious if there are any other alternatives.

Best Answer

You said you didn't want to add an extra column to a number of tables that need to link back to Test1 in this way (ie on ThingA, 3).

How about adding one persisted computed column to TestA that shows the value ThingA if ThingB is 3, and null otherwise?

Then your foreign key references the new column only, based on ThingA in the referencing table.

alter table Test1 add SpecialThingA as
    (case ThingB when 3 then ThingA else null end) persisted;

and

FOREIGN KEY (ThingA) REFERENCES Test1 (SpecialThingA)

In other words - one new column on Test1, rather than a new column on Test2 (to hold '3') and Test3, and ....

Related Question