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
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
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 INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)
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.