Foreign Key with a Constant

Question

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.

Answer 1

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

Answer 2

I think the answer is no.

Foreign Key must reference a UNIQUE CONSTRAINT in the referenced table as per Books Online

It seems like the solution could be a filtered unique index, but that doesn't count as a constraint.

You can check it here:

SELECT 
    i.name
    , i.is_primary_key          -- can be referenced by FK
    , i.is_unique_constraint    -- can be referenced by FK
    , i.is_unique               -- cannot be referenced by FK
FROM sys.indexes AS i 

I don't think you can do it safely without duplicating data - your solution seems best (CHECK constraint on the referencing table)

Indexed view based on Test1 with a filter on ThingB = 3 would probably work as well, but you would be persisting a different column.

I usually avoid triggers as they don't enforce the referential integrity as well as other constraints, but it might be the way to go.

Answer 3

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; 

Answer 4

Using a CHECK constraint :

DROP TABLE IF EXISTS test1;

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

INSERT INTO Test1 VALUES (1,1),(1,2),(2,3);

DROP TABLE IF EXISTS Test2;

DROP FUNCTION IF EXISTS dbo.CodeMatch;
go

CREATE FUNCTION dbo.CodeMatch(@ThingA INT, @ThingB INT)
RETURNS int
AS BEGIN 
    RETURN (
        SELECT CASE WHEN EXISTS
          ( SELECT 1
            FROM Test1
            WHERE @ThingA = ThingA AND @ThingB = ThingB
          )
        THEN 1 ELSE 0 END
    ) 
    END
go


CREATE TABLE Test2
(
    ThingB INT NOT NULL,
    Val INT NOT NULL,
    CHECK(dbo.CodeMatch(1, ThingB)=1)
);

INSERT INTO Test2 VALUES (1,69); --OK
INSERT INTO Test2 VALUES (3,69); --Msg 547 insert statement conflicted with the CHECK constraint

An additional benefit of this design: If you have many tables such as Test2, their CHECK constraints can make use of the same UDF CodeMatch(@ThingA INT, @ThingB INT). Thus, the function can be reused.

Answer 5

Making ThingB unique in Test1, using a single column foreign key on ThingB, and a CHECK constraint to guarantee from Test2 the matching value of ThingA in Test1.

DROP TABLE IF EXISTS test1;

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

INSERT INTO Test1 VALUES (1,1),(1,2),(2,3);

DROP TABLE IF EXISTS Test2;

DROP FUNCTION IF EXISTS dbo.CodeMatch;
go

CREATE FUNCTION dbo.CodeMatch(@ThingA INT, @ThingB INT)
RETURNS int
AS BEGIN 
    RETURN (
        SELECT CASE WHEN EXISTS
          ( SELECT 1
            FROM Test1
            WHERE @ThingA = ThingA AND @ThingB = ThingB
          )
        THEN 1 ELSE 0 END
    ) 
    END
go

CREATE TABLE Test2
(
    ThingB INT NOT NULL,
    Val INT NOT NULL,
    CONSTRAINT FK_Test2_Test1 FOREIGN KEY (ThingB) REFERENCES Test1 (ThingB),
    CHECK(dbo.CodeMatch(1, ThingB)=1)
);

INSERT INTO Test2 VALUES (1,69); --OK
INSERT INTO Test2 VALUES (3,69); --Msg 547 insert statement conflicted with the CHE

Answer 6

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.