Hi All
I have Two Database DB1 Have TBL1, DB2 Have TBL2
and I need a foreign key in the [TBL1] table (which is in DB1 database) is reference to the other table [TBL2] (which is in the DB2 database).
When i try to creating them through SQL, I received the following error message :
Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.
Is that possible to refer foreign key in external database?
If possible, please give solution for this. If not possible, please suggest some alternate way.
Loading
Jignesh TrivediPosted Jul 16, 2012, 12:12 AM
hi,
many databases lack the cross-database referential integrity due to the fact that you cannot have a foreign key accross DB.
one of solution is provided by Santhosh,
you can also SP and In SP combine logic of Inserting as well Checking foreign key.
I think there is only one option manual check.
hope this will help you.
Santhosh Kumar JayaramanPosted Jul 15, 2012, 5:06 AM
Alternate way is to have triggers. When trying to insert or update call the trigger which should check the value in the external database table or else it should rollback.