Foreign Keys between databases

[Jason]

I realize that FK cannot be created across databases. The question I
have is what are the alternatives?

In my thinking I have two choices.

1) First choice (which I read in a different post) is to use a stored
procedure as a constraint to go check the table in the other database.

2) Second choice would be to set up publish/subcribe and replicate the
table across the databases. THEN set up a Foreign Key.

Is there a pro or con (besides having two copies of the table with the
second choice) to either choice? Is there a better way?

[Anith Sen]

Cross-database DRI in SQL Server is impossible. The best workaround is to
use triggers.

Using replication is overkill & not a relevant solution here at all. About
using stored procedures, well, you can do it proactively using triggers,
which is much better, IMO.

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jason (Ja*******@hotmail.com) writes:

I realize that FK cannot be created across databases. The question I
have is what are the alternatives?

In my thinking I have two choices.

1) First choice (which I read in a different post) is to use a stored
procedure as a constraint to go check the table in the other database.
Stored procedure? Possibly you could use a UDF for the task, but don't
do it. While techically possible, the performance penalty are
horrendeous for multi-row inserts.
2) Second choice would be to set up publish/subcribe and replicate the
table across the databases. THEN set up a Foreign Key.

In one word: don't.

It would be interesting to know, why you insist having two databases in
the first place, because that appears to be the root of the problem.

But if you must have it that have that way, use triggers. Note that
you need triggers on both tables. On the parent to check deletes
and updates, and one of the child to check insert and updates.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jason]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Jason (Ja*******@hotmail.com) writes:

I realize that FK cannot be created across databases. The question I
have is what are the alternatives?

In my thinking I have two choices.

1) First choice (which I read in a different post) is to use a stored
procedure as a constraint to go check the table in the other database.

Stored procedure? Possibly you could use a UDF for the task, but don't
do it. While techically possible, the performance penalty are
horrendeous for multi-row inserts.

2) Second choice would be to set up publish/subcribe and replicate the
table across the databases. THEN set up a Foreign Key.

In one word: don't.

It would be interesting to know, why you insist having two databases in
the first place, because that appears to be the root of the problem.

But if you must have it that have that way, use triggers. Note that
you need triggers on both tables. On the parent to check deletes
and updates, and one of the child to check insert and updates.

My reasoning (which could be completely wrong since I am new to
databasing) for multiple databases is a "logically" grouping of
tables. A table that could be used for different purposes would only
have to be created and maintained in one place. In my case I have a
"Supporting" database which has tables with information like country
listings and currency listings. My "Trading" database contains tables
with trades, orders, securities, exchanges, etc. If I needed, I could
create an "HR" database which would also reference tables in
"Supporting".

One of the columns in the Trading.Exchanges table is a row id to a row
in the Supporting.Countries table. While I guess I could just have
multiple copies of the same table in different databases, in my
opinion that is not a very clean solution.

- Jason

[Erland Sommarskog]

Jason (Ja*******@hotmail.com) writes:

My reasoning (which could be completely wrong since I am new to
databasing) for multiple databases is a "logically" grouping of
tables. A table that could be used for different purposes would only
have to be created and maintained in one place. In my case I have a
"Supporting" database which has tables with information like country
listings and currency listings. My "Trading" database contains tables
with trades, orders, securities, exchanges, etc. If I needed, I could
create an "HR" database which would also reference tables in
"Supporting".

Alright, I think I have a solution to your problem: cram it all into
one database.

I understand your reasoning, but using multiple database in an application
is cumbersome. For one: if you make hard-coded references to tables or
stored procedures in the other database, you have a gross problem if you
are asked to set up a second test environment on the same server.

Actually, the system I work with, falls into modules like you describe
above. You can identify system data like countries and currencies, base
data like securities and accounts, transaction data like notes and
positions, order data etc etc. Ok, so we are not considering to have
an HR module (if you mean Human Resources), but not all of our
customers gets all tables.

To this end, we have introduced a subsystem concept. A subsystem is
a set of tables, types, stored procedures etc, and one subsytstem can
depend on another. (But the dependencies should not be circular.) And
to support this, we have a tool, which you can find at
http://www.abaris.se/abaperls/ if you are curious. It's freeware.
One of the columns in the Trading.Exchanges table is a row id to a row
in the Supporting.Countries table. While I guess I could just have
multiple copies of the same table in different databases, in my
opinion that is not a very clean solution.

Having copies of same table in multiple SQL databases is not a problem.
That's just your binary code. Your source code for the table should be
in a version-control system.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp