Referential integrity across DB's


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Referential integrity across DB's

  1. #1
    Deb H. Guest

    Referential integrity across DB's


    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...

  2. #2
    D. Patrick Hoerter Guest

    Re: Referential integrity across DB's

    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




  3. #3
    Deb H. Guest

    Re: Referential integrity across DB's


    "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?
    >



  4. #4
    D. Patrick Hoerter Guest

    Re: Referential integrity across DB's

    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



  5. #5
    DaveSatz Guest

    Re: Referential integrity across DB's

    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...




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center