Prev: restricting access to sp_table_statistics2_rowset
Next: exec usp_MyProc cast(@MyParam as int)
From: Martin Plechsmid on 26 Feb 2010 05:44
Hello.

I have two tables, Customer and Order. If they were in one database, I would
create a foreign key from Order to Customer, but they are not, they are in
different databases (on the same server). The databases are maintained
independently, so I'd rather put an adapter (wrapping the Customer table and
implemented as a db view) between the databases. The view references the
Customer table, and the Order would reference the view. That's the idea.

But I'm unable to create a foreign key from Order to the view (Msg 1768:
"Foreign key ... references object ... which is not a user table"). I'm not
saying it's a good idea to use foreign keys to keep reference integrity
between two databases, but what other options do I have?
(I must mention that the database containing the Customer table is not under
my control, so I hesitate to insert new columns like a Guid-id. Though, when
desperate I would do that.)

Thanks,
Martin.


From: Tibor Karaszi on 26 Feb 2010 06:27
Short answer is that you can have an FK across databases. You can have a
trigger to maintain data integrity, but think hard before you do that. What
if you restore one of the databases to an earlier point in time!

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:OZvIBDttKHA.3408(a)TK2MSFTNGP06.phx.gbl...
> Hello.
>
> I have two tables, Customer and Order. If they were in one database, I
> would create a foreign key from Order to Customer, but they are not, they
> are in different databases (on the same server). The databases are
> maintained independently, so I'd rather put an adapter (wrapping the
> Customer table and implemented as a db view) between the databases. The
> view references the Customer table, and the Order would reference the
> view. That's the idea.
>
> But I'm unable to create a foreign key from Order to the view (Msg 1768:
> "Foreign key ... references object ... which is not a user table"). I'm
> not saying it's a good idea to use foreign keys to keep reference
> integrity between two databases, but what other options do I have?
> (I must mention that the database containing the Customer table is not
> under my control, so I hesitate to insert new columns like a Guid-id.
> Though, when desperate I would do that.)
>
> Thanks,
> Martin.
>
>
From: Martin Plechsmid on 26 Feb 2010 06:43
Hello Tibor,

thank you for your answer.

> Short answer is that you can have an FK across databases.

Yes, I know that. But then I would have to link the database tables
directly, which would result in necessity of modification the Order's
database data model whenever the Customer's database data model changes. To
prevent this I suggested to introduce the adapter. But you cannot create a
foreign key from table to a view!

> What if you restore one of the databases to an earlier point in time!

Good point. So what would you suggest? Please note that the dependency
between the two databases is in one direction only - the Order's db depends
on the Customer's db, but not the other way round.

Thank you,
Martin.



From: Tibor Karaszi on 26 Feb 2010 08:58
>> Short answer is that you can have an FK across databases.
>
> Yes, I know that. But then I would have to link the database tables
> directly, which would result in necessity of modification the Order's
> database data model whenever the Customer's database data model changes.
> To prevent this I suggested to introduce the adapter. But you cannot
> create a foreign key from table to a view!

First, let me say that the "can" should have been "can't". One of those
typos which totally changes the meaning of the text.

So, with that in mind, we established that we cannot have an FK referring to
a table in a different database. Nor can an FK refer to a view.

There's no answer to this. Since restoring either database to an earlier
point in time is a disaster scenario possibility, you can always have
inconsistent data. This is one of the facts of spreading the data over
several databases! You can use log backups and transaction log markers to
allow restore of several databases' log backups to such a log marker, but
that is pretty burden of the DBA. If you decide to go this route (if you
decide to have related tables in different databases in the first place),
then consider using a trigger to reflect a modification on the the database.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:#$iP$jttKHA.6140(a)TK2MSFTNGP05.phx.gbl...
> Hello Tibor,
>
> thank you for your answer.
>
>> Short answer is that you can have an FK across databases.
>
> Yes, I know that. But then I would have to link the database tables
> directly, which would result in necessity of modification the Order's
> database data model whenever the Customer's database data model changes.
> To prevent this I suggested to introduce the adapter. But you cannot
> create a foreign key from table to a view!
>
>> What if you restore one of the databases to an earlier point in time!
>
> Good point. So what would you suggest? Please note that the dependency
> between the two databases is in one direction only - the Order's db
> depends on the Customer's db, but not the other way round.
>
> Thank you,
> Martin.
>
>
>
From: Martin Plechsmid on 26 Feb 2010 10:20
Thank you!

> First, let me say that the "can" should have been "can't".

I was sure you can, but I tested that - and you're right. I always threat
MSSQL databases like a synonym to Oracle schemas - and evidently the analogy
isn't completely correct.

> There's no answer to this. Since restoring either database to an earlier
> point in time is a disaster scenario possibility, you can always have
> inconsistent data. This is one of the facts of spreading the data over
> several databases! You can use log backups and transaction log markers to
> allow restore of several databases' log backups to such a log marker, but
> that is pretty burden of the DBA.

One of my personal alternatives was to replicate the data from one db to the
other. But that wouldn't solve anything. When you restore one database, you
have inconsistencies again.

> If you decide to go this route (if you decide to have related tables in
> different databases in the first place)

That's not up to my decision. Customer's database is controled by one
application, Order's db by another, so it's natural that the databases are
separate. But every application needs to work with customers somehow (e.g.
by attaching orders to customers that sent them). And we want to have a
central evidency of customers, not separate evidency for each application.
Thus we need either to link records in one db to customers in the central
evidency, or replicate customers from the central evidency to individual
application databases.

Martin.


 |  Next  |  Last
Pages: 1 2 3
Prev: restricting access to sp_table_statistics2_rowset
Next: exec usp_MyProc cast(@MyParam as int)