Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    England
    Posts
    15

    Unanswered: enforcing referential integrity on distr. DBs

    I am trying to create a basic order distributed DB. The DB will be stored at two locations with one location having 3 out of 5 tables stored at its site. I have a product table which is stored at the main site. This product table is related to an orderline table which contains the product_no as a foreign key.

    I am unsure how this will work on the site with only 3 tables as it does not contain the product table so the link will not work meaning I need to think of another way in which to enforce referential integrity.

    Any help or suggestions on this would be great.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi donnie_darko,
    I guess, the following should help you.

    (1) CREATE the two database links one from A to B and second from B to A. ( Thinking You have two database A and B on two different sites)

    (2) On A site where Product table exists, Create the PUBLIC SYNONIM .
    for e.g

    PHP Code:
    CREATE PUBLIC SYNONYM orderline
             
    FOR orderline@DBLinkFromAtoB
    (3) On site B, where orderline resides,

    PHP Code:
    CREATE PUBLIC SYNONYM product
               
    FOR product@DBLinkFromBtoA;

              
    ALTER TABLE orderline ADD
              CONSTRAINT 
    <fk_product_noFOREIGN KEY
              REFERENCES product 
    (product_no); 
    I have not checked for any syntex error. So please correct it if it has any.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As OP stated, what happens to the application when the network
    connection between the two sites is DOWN?

    IMO, the design/architecture is a recipe for disaster.
    I can't imagine any rational manager accepting to pay
    for the actual implementation of the design as described.


    Originally posted by Hings
    Hi donnie_darko,
    I guess, the following should help you.

    (1) CREATE the two database links one from A to B and second from B to A. ( Thinking You have two database A and B on two different sites)

    (2) On A site where Product table exists, Create the PUBLIC SYNONIM .
    for e.g

    PHP Code:
    CREATE PUBLIC SYNONYM orderline
             
    FOR orderline@DBLinkFromAtoB
    (3) On site B, where orderline resides,

    PHP Code:
    CREATE PUBLIC SYNONYM product
               
    FOR product@DBLinkFromBtoA;

              
    ALTER TABLE orderline ADD
              CONSTRAINT 
    <fk_product_noFOREIGN KEY
              REFERENCES product 
    (product_no); 
    I have not checked for any syntex error. So please correct it if it has any.

    HTH

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I thought "link" as Database Link.

    Originally posted by anacedent
    As OP stated, what happens to the application when the network
    connection between the two sites is DOWN?

    IMO, the design/architecture is a recipe for disaster.
    I can't imagine any rational manager accepting to pay
    for the actual implementation of the design as described.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Oct 2003
    Location
    England
    Posts
    15
    Thanks for the replies.

    There is actually an orderline and product table on site A and an orderline table but no product table on site B. Thats the problem I need to solve for site B. I was unclear on this.

    This isn't a professional project, but a piece of College work to teach us about setting up a distributed DB. I have to think of ways to enforce referential integrity between a table to a fragmented table. I am not sure if the tables I was referring to in my first post are the correct ones as I don't think they will be fragmented. I have a branch table table which will be fragmented by location and a customer table containing a FK called branch_no.

    Customer details will be kept local to each branch but they need to access each other's tables. I think the question may be referring to this. I need to look at it some more.

    Another question, I need to ensure that I have unique primary keys on the fragmented tables. Is the best way just to start the primary key with a 1 for branch number 1 and a 2 for branch number 2? EG, 11, 12, 13, 14, etc and 21, 22, 23, 24, etc?
    Last edited by donnie_darko; 02-22-04 at 06:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •