Cross Forign Key Between Two Database

Question

0.00/5 (No votes)

See more:

Hi All
I have Two Database DB1 Have TBL1, DB2 Have TBL2
and I need a foreign key in the [TBL1] table (which is in DB1 database) is reference to the other table [TBL2] (which is in the DB2 database).

When i try to creating them through SQL, I received the following error message :

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.
Is that possible to refer foreign key in external database or not?

If possible, please give solution for this. If not possible, please suggest some alternate way.

Posted 14-Jul-12 22:04pm

Mustafa Salman

Add a Solution

Comments

Bala Selvanayagam 15-Jul-12 7:02am

You can not have cross-database foreign key but can use triggers instead.

Basically, you needs to check the data validation on insertion using trigger and raise an error, if needed

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-07-14T22:28:00

Solution 1

The error message is pretty explicit:
"Cross-database foreign key references are not supported"

So the answer is in the question: "No".

Posted 14-Jul-12 22:28pm

OriginalGriff

Comments

Mustafa Salman 15-Jul-12 4:31am

Hi
I Know that but I Ask for Another Solution

barneyman · Answer 2 · 2012-07-14T22:33:00

Solution 2

as OG stated, it's not possible

Alternatives are, have the SP you use to manipulate the row make the assertion

or put a trigger in there - I'd go with the first one

Posted 14-Jul-12 22:33pm

barneyman