Solved

Cross Database Foreign Key Relationship.

Posted on 2009-06-29
8
351 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
Comment Utility
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
Comment Utility
Hi chapmandew,

Very interesting.

How could we do it?

Thank you.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
Ok.

It is safe use kind of trigger?

Thank you.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
sure...
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Ok.

Thank you very much for your help.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

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.…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now