SQL Server Foreign Key relationship against two tables

Question

I have a DB where 3 of the tables have a FK relationship inasmuch as:

Table A has 2 (relevant) fields:
TypeId (int)
LinkId (int)

Tables B and C each have a primary key that maps to the LinkId in Table A. If the TypeId in Table A is 1 then the LinkId maps to the primary key in Table B. If it's 2 then it maps to the primary key in Table C.

In this badly designed DB is there anyway to enforce referential integrity among these tables? i.e. Is it possible to prevent SQL Server from inserting a record in Table A if the corresponding record does not exist in Tables B or C?

I guess it would be possible with before triggers but it is not trivial to build them correctly. Is it an option to redesign the tables? — Mikael Eriksson, May 06 '12 at 17:53
So foreign keys are from TBL_A to B or C? OR from B,C to A? Could you post foreign keys definition? — Damir Sudarevic, May 06 '12 at 19:09

jenson-button-event · Answer 1 · 2012-05-06T18:21:52.337

You could use two new link tables and drop LinkId from table A

AB (Aid, Bid)
AC (Aid, Cid)

Depends on your app though and the control you have over the inserting...

You don't have to use built in constraints - you can write custom ones with triggers of course if you wanted to keep that structure.

something like:

   CREATE TRIGGER trigger_name ON A
      FOR INSERT, UPDATE

      AS

     declare @err varchar(50)

     select @err = case 
       when inserted.typeid = 1 and not exists(select 0 from B where id=inserted.linkid) then
            @err = 'No link record exists in B.'
       when inserted.typeid = 2 and not exists(select 0 from C where id=inserted.linkid) then
            @err = 'No link record exists in C.'
       else @err = null     
      end
      from inserted

     if @err is not null RAISERROR (@err, 16, 1 )

SQL Server Foreign Key relationship against two tables

1 Answers1