CODEWITHSUNDEEP
sqlsql-serverforeign-keys
adhocgeek
adhocgeek

Reputation: 1457

Can the table referenced by a foreign key depend on the contents of another field?

Setting aside for a moment the sanity of whether this is a good idea or not, I was wondering whether it would be possible to set up a field which links to more than two tables and still be able to enforce referential integrity?

e.g. I'd like to be able to create foreign key definitions something like this :

create table TestTable
(
    Id int identity not null primary key,
    SourceId int not null,
    SourceTable varchar(100) not null
    /* some other common data goes here */

    constraint FK_TestTable_TableA foreign key (SourceId) references TableA(Id) when TestTable(SourceTable) = 'TableA'
    constraint FK_TestTable_TableB foreign key (SourceId) references TableB(Id) when TestTable(SourceTable) = 'TableB'
)

Is there a pattern for achieving this kind of behaviour, or if I go down this route am I simply doomed to the creeping horror that is a lack of referential integrity?

Upvotes: 4

Views: 470

Answers (1)

StuartLC
StuartLC

Reputation: 107237

No, this isn't possible without workarounds such as @Damien's

An alternative workaround is to use triggers to check up on the integrity.

Here's an INSTEAD OF trigger implementation - SqlFiddle here

CREATE TRIGGER t_TestTable ON TestTable INSTEAD OF INSERT
AS
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO TestTable(SourceID, SourceTable)
            SELECT i.SourceID, i.SourceTable --, + i.Other field values
            FROM INSERTED i
                WHERE (i.SourceTable = 'TableA' AND EXISTS (SELECT * FROM TableA where ID = i.SourceID))
                OR (i.SourceTable = 'TableB' AND EXISTS (SELECT * FROM TableB where ID = i.SourceID));
        -- IF @@ROWCOUNT = 0 THROW /  RAISERROR ?
    END;
GO

You'll also need to cover UPDATES on TestTable, and cover UPDATES and DELETES on TableA / TableB, and also determine what to do in the event of FK violations (ignoring the data probably as I've done isn't a good strategy)

Instead of reinventing the wheel, a better design IMO is to use table inheritance - e.g. make TableA and TableB inherit from a common base ancestor Table which has a unique Primary Key common to both tables (and add the SourceTable` table type qualifier to the base table). This will allow for direct RI.

Upvotes: 1

Related Questions