Solved

Cross Database Foreign Key Relationship.

Posted on 2009-06-29
8
352 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:emi_sastra
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24735743
You can create a trigger to enforce the integrity between the 2 tables in the different dbs.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24735769
Hi chapmandew,

Very interesting.

How could we do it?

Thank you.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24735788
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24735823
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24735835
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24736047
Ok.

It is safe use kind of trigger?

Thank you.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24736181
sure...
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24736292
Ok.

Thank you very much for your help.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now