Referential integrity across DB's

[Deb H.]

Can anyone give me some tips on how to best maintain referential integrity
across databases? I have multiple databases whose tables contain foreign
keys referencing primary keys in other database tables. I want to make sure
that a deletion cannot occur if tables in other databases are referencing
the record to be deleted. In other words, I don't want to orphan these records.

I was thinking of using triggers, but not sure if this is best.

Thanks...

[D. Patrick Hoerter]

Deb,

Deb H. <deb_hunter@carolinadreamers.com> wrote in message
news:3a5648f1$1@news.devx.com...
>
> Can anyone give me some tips on how to best maintain referential integrity
> across databases? I have multiple databases whose tables contain foreign
> keys referencing primary keys in other database tables.

First off, you don't have that at all if you're using SQL Server. The
product doesn't support cross-database FK's. (I know you knew that, I'm just
bustin' your chops.) ;-) You really shouldn't refer to them as Foreign Keys,
though, because unless the engine is enforcing the relationships, they only
exist in your mind.

> I want to make sure
> that a deletion cannot occur if tables in other databases are referencing
> the record to be deleted. In other words, I don't want to orphan these
records.
>
> I was thinking of using triggers, but not sure if this is best.
>

Triggers are the only solution that I know of for this type of thing, but
they will never perform as well as a real Foreign Key, because the are
evaluated much too late in the transaction. You really should try to get
this data in the same database if it is truly related.

Regards,
D. Patrick Hoerter

[Deb H.]

"D. Patrick Hoerter" <phoerter@NO_SPAMMERS_bellatlantic.NET_net> wrote:
>Deb,
>
>Deb H. <deb_hunter@carolinadreamers.com> wrote in message
>news:3a5648f1$1@news.devx.com...
>>
>> Can anyone give me some tips on how to best maintain referential integrity
>> across databases? I have multiple databases whose tables contain foreign
>> keys referencing primary keys in other database tables.
>
>First off, you don't have that at all if you're using SQL Server. The
>product doesn't support cross-database FK's. (I know you knew that, I'm
just
>bustin' your chops.) ;-) You really shouldn't refer to them as Foreign Keys,
>though, because unless the engine is enforcing the relationships, they only
>exist in your mind.
>
>> I want to make sure
>> that a deletion cannot occur if tables in other databases are referencing
>> the record to be deleted. In other words, I don't want to orphan these
>records.
>>
>> I was thinking of using triggers, but not sure if this is best.
>>
>
>Triggers are the only solution that I know of for this type of thing, but
>they will never perform as well as a real Foreign Key, because the are
>evaluated much too late in the transaction. You really should try to get
>this data in the same database if it is truly related.
>
>Regards,
>D. Patrick Hoerter
>
>I don't really have a choice here. There is a database which consists of
names and addresses and other general information. All of the other databases
pull name and address info from this one. It's like a centralized address
book. I guess one thing I could do is to not allow deletions from the name
and address database.

What do you think?
>

[D. Patrick Hoerter]

Deb,

> >I don't really have a choice here. There is a database which consists of
> names and addresses and other general information. All of the other
databases
> pull name and address info from this one. It's like a centralized address
> book. I guess one thing I could do is to not allow deletions from the name
> and address database.
>
> What do you think?
> >
>

Here's an idea: In the 'central' database, put a ReferenceCount column in
the main address table. Whenever another database makes use of an address,
just increment this column by one. If another database ceases to use that
address, decrement the reference counter. That way, if you want to delete an
address in the central system, make the rule that you can only delete it if
its ReferenceCount column is zero. That way, you could potentially use this
central address database from an unlimited number of areas, and not have to
check all of them when you go to do a delete.

Otherwise, you're back to the triggers solution.

Regards,
D. Patrick Hoerter

[DaveSatz]

You could perform a replication from the source DB to the other DB's and
then create the actual FK relationships in the destination DB's.
Unfortunately, you could still delete from the source without getting a RI
violation, I would write triggers in the source DB to check the destination
DB's before deleting.

In a low delete volume situation -- Even if you did not write the triggers,
the replication would fail because the delete would violate RI on one of the
destination databases, but you would not be orphaning the row. So it w/b
ugly, but you could manually add the row back to the source if it is was
deleted.

--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"Deb H." <deb_hunter@carolinadreamers.com> wrote in message
news:3a5648f1$1@news.devx.com...
>
> Can anyone give me some tips on how to best maintain referential integrity
> across databases? I have multiple databases whose tables contain foreign
> keys referencing primary keys in other database tables. I want to make
sure
> that a deletion cannot occur if tables in other databases are referencing
> the record to be deleted. In other words, I don't want to orphan these
records.
>
> I was thinking of using triggers, but not sure if this is best.
>
> Thanks...