Database constraint to check against specific constraints dependent on an input value?

Question

I'm running maintenance on a database program with no foreign key constraints, even though it really should have...

There is a Logging table which has a ParentID column, and a ParentType column. The entry in the ParentType column determines which table ParentID references.

What I'm looking for is for ideas on the best way to add this input-dependent foreign key constraint... abomination...

I'd love to be able to just throw out the whole back end of this program and start again, but it's already unreliable as hell without me breaking everything. Tightening up the back end seems to be my best best at this point. The only real alternative seems to be to throw out the entire program.

So, yeah, ideas on the best way to constrain the data in this column on the SQL side?

EDIT: I can tell this unclear already...

Right, so I have have the Logging table, then TableOne and TableTwo. If the row being entered into Logging tries to add a row with 1 in the ParentType column, then the ParentID must appear in TableOne, if ParentType is 2, ParentID must appear in TableTwo.

Answer 1

Your best bet may be to implement this rule as an INSTEAD OF trigger on the Logging table, where you could check the IDs against the appropriate table and reject the INSERT/UPDATE if a FK violation would occur. In rough pseudo-code, something like:

CREATE TRIGGER tr_io_iu_Logging ON Logging
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF UPDATE(ParentID) BEGIN
        IF NOT EXISTS(SELECT 1 FROM Inserted i LEFT JOIN TableOne t1 ON i.ParentID = t1.ID WHERE i.ParentType=1 AND t1.ID IS NULL)
            /* perform the insert */
        ELSE 
           /* raise error */

        IF NOT EXISTS(SELECT 1 FROM Inserted i LEFT JOIN TableTwo t2 ON i.ParentID = t2.ID WHERE i.ParentType=2 AND t2.ID IS NULL)
            /* perform the insert */
        ELSE 
           /* raise error */
    END /* IF */    
END /* trigger */

Answer 2

Assuming you are entering data via an SP, I'd consider using a UDF with parameters for the value and tabletype that performs a lookup for validation against the appropriate table in the tabletype. This won't be a screamer in terms of performance, but you'll be able to put the call in prior to insert for validation purposes in the insert SP, and you'll be able to centralize the validation in the UDF.

Answer 3

Create two persisted computed columns, like this:

CarID AS (CASE WHEN Type='Car' THEN ParentID END)

TruckID AS (CASE WHEN Type='Truck' THEN ParentID END)

and have FK refer them it to your tables.