Vryjdfkk

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:

• https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=edba40478f104ac017c3444138a07762

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

I think the combination of a surrogate key on dbo.Test1 and a trigger executed after both INSERT and UPDATE statements, specifically an INSTEAD OF trigger, is the answer here.

And might look something like this (fiddle):

Schema

create table dbo.Test1 (

    Id int identity primary key,

    ThingA int not null,

    ThingB int not null

);



create table dbo.Test2 (

    Id int identity primary key,

    Test1Id int not null foreign key references Test1 (Id),

    Val int not null

);

After INSERT Trigger

create trigger test2ThingBCheck_Insert 

on dbo.Test2  

instead of INSERT 

as  

begin



    insert into dbo.Test2 (Test1Id, Val)

    select

        t.Id

        ,i.Val

    from 

        Test1 t 

    join 

        inserted i 

        on i.Test1Id = t.Id

    where

        t.ThingB = 3;

end;

After UPDATE Trigger

create trigger test2ThingBCheck_Update

on dbo.Test2  

instead of Update 

as  

begin

    update

        t2

    set

        Val = i.Val

    from

        dbo.Test2 t2

    join

        inserted i

        on i.Id = t2.Id        

    join

        dbo.Test1 t1

        on t1.Id = i.Test1Id

    where

        t1.ThingB = 3;

end; 

If you know the constant value of ThingB, then I suggest to leave the column "ThingB" away. Instead let the business logic add the constant value. What the value exactly is can be stored in another table or in some setting.

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