Relation b/w different database with different tables

No, you can't directly create foreign key reference between two databases. You can do a workaround through trigger:

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Suppose there are two databases DB1 and DB2. Your parent table(tabl1) is present in DB1 and child table(tab2) in DB2 which is foreign key to tab1 of DB1 database.

Create below trigger in DB2 on table tab2

CREATE Trigger dbo.MyTableTrigger ON dbo.tab2, After Insert, Update

BEGIN

IF NOT Exists(select PK from DB1.dbo.tbl1 where PK in (Select FK from inserted) BEGIN

-- Handle the Referential Error Here

END

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
No, you can't directly create foreign key reference between two databases. You can do a workaround through trigger:

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Suppose there are two databases DB1 and DB2. Your parent table(tabl1) is present in DB1 and child table(tab2) in DB2 which is foreign key to tab1 of DB1 database.

Create below trigger in DB2 on table tab2
CREATE Trigger dbo.MyTableTrigger ON dbo.tab2, After Insert, Update
As
BEGIN
   IF NOT Exists(select PK from DB1.dbo.tbl1 where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END
END
0