Solved

Cross Database Foreign Key Relationship.

Posted on 2009-06-29
8
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to find substring in SQL 5 52
Limit number of characters returned to 999 9 39
Need multiple Group By's 8 52
Using a SUBQUERY for the set variable 10 24
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 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