Solved

Cross Database Foreign Key Relationship.

Posted on 2009-06-29
8
353 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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