7

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.

  • I fear there is no perfect solution in current implementations of SQL DBMSs. But you could use TINYINT, if the number possible values of ThingB is small. – ypercubeᵀᴹ Jun 7 '18 at 21:09
  • @ypercubeᵀᴹ Yeah, it's a SMALLINT on prod, I just wish there were a better way to associate it. I might get rid of A entirely, as I don't actually need it, and just do the referential-integrity via ThingA, thus allowing me to remove ThingB. – Der Kommissar Jun 7 '18 at 21:24
  • If I have time, I'll add an answer later with my thoughts on this. If not, during the weekend. I had a similar/related question, some years ago. Are there DBMS that allow a Foreign Key that References a View (and not only base tables)? – ypercubeᵀᴹ Jun 7 '18 at 21:28
  • @ypercubeᵀᴹ I appreciate it. Scratching my head here, and my solutions are only getting more convoluted. – Der Kommissar Jun 7 '18 at 21:30
  • Is there a reason why you're choosing not to use a surrogate identity-based key coupled with a UNIQUE constraint on (ThingA, ThingB)? – John Eisbrener Aug 13 '18 at 14:15
0

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; 
0

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.

0

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

  • Didnt work for me on SQL server because persisted column in 'nchar' and the column that Im trying to create the relationship is 'int' – juanora Jan 9 at 16:19
  • @juanora - if your 'ThingA' column is an integer, then update the case statement to: (case ThingB when 3 then cast(ThingA as int) else null end) persisted; - this should ensure the calculated column is defined as an int. – youcantryreachingme Jan 11 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.