Cross-database foreign key references are not supported. Workaround?

Given that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.

A

Post by Charles Law
When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still
like to have a constraint based on rows in a table of another database
(running in the same instance).
Is there any way round this, or am I trying to do something unreasonable?
If it's not possible, what would others normally do in this situation?
TIA
Charles

Charles Law

2008-01-07 17:13:02 UTC

Hi Aaron

Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.

Charles

Post by Aaron Bertrand [SQL Server MVP]
Given that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you
think you need a "copy" of the data.
A

David Portas

2008-01-07 18:59:04 UTC

Post by Charles Law
Hi Aaron
The databases are separate since the look-up data will change from
customer to customer, and it is more convenient to be able to 'plug in' a
new set of static data if it is in a separate database.

I think your question demonstrates exactly the opposite. It is much LESS
convenient to put the static data in a separate database precisely because
that data is very likely to be referenced by a foreign key.

--
David Portas

Charles Law

2008-01-07 19:09:32 UTC

Hi David

I can't argue with your logic there.

I'll have to give this some more careful thought. Perhaps I am creating more
problems than I solve by separating the static data from the rest.

Charles

Post by David Portas

Anith Sen

2008-01-07 17:07:03 UTC

Post by Charles Law
Is there any way round this, or am I trying to do something unreasonable?
If it's not possible, what would others normally do in this situation?

You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.

--
Anith

Charles Law

2008-01-07 17:17:39 UTC

Hi Anith

Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.

Cheers.

Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

John Bell

2008-01-07 17:27:02 UTC

Hi

There is also the option of using a function in the constraint.

John

Post by Charles Law
Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

Charles Law

2008-01-07 18:57:46 UTC

Hi John

Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?

Charles

Post by John Bell
Hi
There is also the option of using a function in the constraint.
John

Post by Charles Law
Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

Alex Kuznetsov

2008-01-07 19:14:05 UTC

Post by Charles Law
Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles

Post by John Bell
Hi
There is also the option of using a function in the constraint.
John

Post by Charles Law
Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
winner.

Charles Law

2008-01-08 00:50:34 UTC

I guess that's what one would expect, but good to have it confirmed (on
2000).

Charles

Post by Charles Law
Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles

Post by John Bell
Hi
There is also the option of using a function in the constraint.
John

Post by Charles Law
Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
winner.

John Bell

2008-01-08 07:55:00 UTC

Hi Charles

There is an example in the link Alex posted.

John

Post by Charles Law
Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles

Post by John Bell
Hi
There is also the option of using a function in the constraint.
John

Post by Charles Law
Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles

Post by Charles Law
Is there any way round this, or am I trying to do something
unreasonable? If it's not possible, what would others normally do in
this situation?

You are not trying to do anything unreasonable. In fact, it is a very
common issue. Since DRI between databases are not supported, people
usually use triggers (a way of procedural referential integrity) to get
the job done.
--
Anith

Russell Fields

2008-01-07 17:05:56 UTC

Charles,

By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)

RLF

Charles Law

2008-01-07 17:15:30 UTC

Hi Russell

Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.

Cheers.

Charles

Post by Russell Fields
Charles,
By definition, constraints only work within a database. The only
mechanism that I have used for cross-database constraints is triggers.
(And only occasionally.)
RLF

Alex Kuznetsov

2008-01-07 17:19:30 UTC

Post by Charles Law
When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
Charles

Charles,

I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.

Charles Law

2008-01-07 19:06:05 UTC

Post by Alex Kuznetsov
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution

Good point.

Post by Alex Kuznetsov
Also be aware that sometimes trigger do not fire.

That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?

Charles

Post by Charles Law
When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
Charles

Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.

Alex Kuznetsov

2008-01-07 19:12:45 UTC

Good point.

Post by Alex Kuznetsov
Also be aware that sometimes trigger do not fire.

That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles

not random. Described here:

http://www.devx.com/dbzone/Article/31985

Charles Law

2008-01-08 23:24:12 UTC

Thanks. It looks, though, like they do not fire if the settings are
incorrect, or if someone changes them. So long as the rules are followed
then there shouldn't be a problem?

Charles

Good point.

Post by Alex Kuznetsov
Also be aware that sometimes trigger do not fire.

That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles

http://www.devx.com/dbzone/Article/31985

Alex Kuznetsov

2008-01-09 00:36:11 UTC

Post by Charles Law
Thanks. It looks, though, like they do not fire if the settings are
incorrect, or if someone changes them. So long as the rules are followed
then there shouldn't be a problem?
Charles

Good point.

Post by Alex Kuznetsov
Also be aware that sometimes trigger do not fire.

That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles

http://www.devx.com/dbzone/Article/31985

Also use database permissions to make sure that your users cannot
truncate your parent table. Truncate also does not fire triggers. Bulk
insert also does not fire them either. Also dropping recreating a
parent table is a way around your triggers...

Charles Law

2008-01-09 09:30:57 UTC

Post by Alex Kuznetsov
Also use database permissions to make sure that your users cannot
truncate your parent table. Truncate also does not fire triggers. Bulk
insert also does not fire them either. Also dropping recreating a
parent table is a way around your triggers...

Ok. Point taken.

Cheers

Charles

Good point.

Post by Alex Kuznetsov
Also be aware that sometimes trigger do not fire.

That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles

http://www.devx.com/dbzone/Article/31985

unknown

2008-05-12 19:00:07 UTC

You can achieve the result through Check contraint using a function to check the foreign key relationship.

Please follow the link in my answer for more details :

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3037915&SiteID=17

Thanks
Naras.

Alex Kuznetsov

2008-05-12 19:09:32 UTC

Post by unknown
You can achieve the result through Check contraint using a function to check the foreign key relationship.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3037915&Site...
Thanks
Naras.

Let me repeat myself:

I hope you realize that your solution is not
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. This is why FK constraints between databases are not
implemented in the first place. You cannot achieve fully functional
database integrity using your approach. All you can get is false sense
of security.

Aaron Bertrand [SQL Server MVP]

2008-05-12 19:18:16 UTC

I'm with Alex on this one. If one of your databases goes south, good luck
getting back in sync!

Post by unknown
You can achieve the result through Check contraint using a function to check
the foreign key relationship.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3037915&SiteID=17
Thanks
Naras.

Erland Sommarskog

2008-05-12 22:13:04 UTC