Cross Database Foreign Key Relationship.

Hi,

I have separate database for Master and Transaction.
Due to creating the foreign key of different database, the message shows that it is not supported.

Is there any way (trick) to overcome this beside make them into one database?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
You can create a trigger to enforce the integrity between the 2 tables in the different dbs.
0
emi_sastraAuthor Commented:
Hi chapmandew,

Very interesting.

How could we do it?

Thank you.
0
chapmandewCommented:
well, on your table transaction, just make sure that the records exist in the other table....kinda like this

use db1

create trigger tr_tranactions
on transaction
for insert, update
as
begin
if not exists(select 1 from db2..master m join inserted i on m.masterid = i.masterid)
rollback transaction
end

this is just a rough outline...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

emi_sastraAuthor Commented:
I see know, manual checking by application or trigger.

When user try to delete the data from the table of Master, then we should check it also it has been used by child table or not?

Thank you.
0
chapmandewCommented:
yes, you'd definetly have to do that too....so, on the master, check for deletes and if they're in the other table.  on the transaction table, make sure any inserts or updates are in the master table...make sense?
0
emi_sastraAuthor Commented:
Ok.

It is safe use kind of trigger?

Thank you.
0
chapmandewCommented:
sure...
0
emi_sastraAuthor Commented:
Ok.

Thank you very much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.