4

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?

adhocgeek
  • 1,437
  • 1
  • 16
  • 30
  • It can only really be done by adding computed columns to this table (e.g. `TableASourceId` and `TableBSourceId` such that they're `SourceId` when the table matches and `null` when the table is wrong) and then defining the foreign keys from those columns instead of from the `SourceId` column. – Damien_The_Unbeliever Dec 02 '13 at 11:37

1 Answers1

1

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.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Bleh. I understand the use of triggers but that looks like a really horrible way to manage this. I appreciate you outlining the method though. Can you elaborate on the table inheritance technique? I'm afraid I can't quite grasp what you mean. – adhocgeek Dec 02 '13 at 14:08
  • Agreed - triggers (or rules, or hacks on computed columns) are all smelly. Table inheritance is discussed in excellent detail [here](http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database#3579462), specifically the 3rd `Class Table Inheritance` option, whereby a common primary key on the `abstract` superclass is shared by concrete `subtables`. The superclass PK can then be safely referenced by other tables (and by enforcing RI between subtable and supertable, SQL Server does all the work needed.) – StuartLC Dec 02 '13 at 14:22
  • That makes things a lot clearer, thanks! I don't suppose you have a link handy for how to map such a table hierarchy with Fluent NHibernate...? It looks like that wouldn't be a trivial thing to do, but I'll admit I haven't got my head round the solution fully yet :) – adhocgeek Dec 02 '13 at 14:35
  • ..Now that I've had a longer look at the class table inheritance option, I'm fairly convinced that it doesn't really solve the (OCD) tidiness problem I was initially trying to address because, although I'd end up with referential integrity, I'd still end up with awful unwieldy table names, which I was trying to avoid :( – adhocgeek Dec 02 '13 at 15:04
  • Yup, if inheritance doesn't make sense in your model, then rather leave it off. You can always resort to frequent integrity checking scripts which do the 'conditional' integrity checking between the 3 tables. – StuartLC Dec 02 '13 at 16:13