Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    43

    Do relationships have to be bidirectional?

    In designing a schema that has a bidirectional relationship in the domain model, would the schema also have a bidirectional relationship?

    Meaning, Table1 has a primary key and a foreign key pointing to the primary key of Table2. Does Table2 need a foreign key pointing to the primary key of Table1 as well?

    Is the above scenario ever needed? In fact, is it ever valid? It seems to me that even in a bidirectional relationship, you would never have the above because it would be redundant. Is my reasoning correct?

    Does the above violate any normalization principles?

    Thanks.

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    In your example, Table 2 would not need to have a key pointing back to Table 1. However, such recursive relationships are possible, valid, and are used at times.

  3. #3
    Join Date
    Mar 2006
    Posts
    43
    Thanks for replying.

    If cases when you have a bidirectional relationship in your OO code (meaning one object has a reference to another, and that other object also has a reference to the first), then must you have 'bidirectionality' in your database as well? Or is it always possible to navigate both ways in your database by just having a 'unidirectional' relationship between two entities?

    such recursive relationships are possible, valid, and are used at times
    What are the advantages to designing with redundant foreign keys, or in what cases should I consider using them? If, through SQL, you can always get data from two entities when there is only one foreign key (rather than two) between those two entities, in what cases should your design incorporate the double-foreign key strategy?

    Thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    There is no need to create foreign keys in both directions normally, and to do so would usually be wrong - unless there actually are two different relationships. If you create the same relationship both ways around then you are enforcing a mandatory 1:1 relationship, which may be valid but only rarely.

  5. #5
    Join Date
    Mar 2006
    Posts
    43
    Thanks for your reply. I'm new to this so I can't picture how a bidirectional foreign key relationship enforces a mandatory 1:1 relationship.

    Can't you still make it a 1:m relationship by simply adding, say, 5 more rows in one of the two tables that point to the primary key of a row in the other table?

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Let's see whether this mandates a 1:1 relationship

    vk101 said
    Thanks for your reply. I'm new to this so I can't picture how a bidirectional foreign key relationship enforces a mandatory 1:1 relationship.

    Can't you still make it a 1:m relationship by simply adding, say, 5 more rows in one of the two tables that point to the primary key of a row in the other table?
    Let's see what happens when we do as vk101 suggests.

    There is a table T1 with columns (p1 [primary Key], a1, a2, p2 [Foreign Key from table T2])

    There is a second table T2 with columns (p2 [primary key], b1, b2, p1 [foreign key to Table T1])

    Now let's try and create the bi-directional data as suggested.

    Table T1 (p1, a1,a2, p2)
    ---------
    Row 1: 101, 'S1', 'S2', 201

    Table T2 (p2, b1, b2, p1)
    ---------
    Row 1: (201, 'T1', 'T2', 101)

    You say you want to create five rows in T2 that refer to T1's row with primary key 101. Let's create four more rows in T2.

    Table T2:
    ----------
    Row 2: (202, 'U1', 'U2', 101)
    Row 3: (203, 'V1', 'V2', 101)
    Row 4: (204, 'W1', 'W2', 101)
    Row 5: (205, 'X1', 'X2', 101)

    So far, no problem.

    But remember, a bi-directional relation must exist in both tables!

    Let's see what we have to do for table T1's data. We must add four more rows each with primary key 101 and foreign keys 202, 203, 204 and 205 respectively. This clearly is not possible since it violates the primary key constraint of table T1! Clearly, you can see that there can be only one row with primary key 101 in T1; and hence a bi-directional relationship may have only one value at each end.

    Hence, Tony's statement that a bi-directional relationship mandates a 1:1 relationship.

    Hope that helps.

    Ravi

  7. #7
    Join Date
    Mar 2006
    Posts
    43
    Very well demonstrated ... excellent answer!

    I noticed two things about this, perhaps somebody could tell me whether my thoughts are correct:

    1) This does not enforce 1:1 versus 1:0...couldn't somebody define a row in one table without a corresponding field in the other? Or am I wrong, in that there would have to be in order to validate the foreign key in the first table? (Can you have a foreign key that refers to a primary key in another table that does not yet exist, or has been deleted?)

    2) There would technically be nothing wrong with somebody adding in the 4 extra rows that you added into the database...wouldn't the schema still be tehcnically correct? If so, then does this indicate a heavy responsibility on the users of the database (the code in the programming language accessing the database) to make sure that their SQL explicitly says something like WHERE pktable1=fktable2 AND pktable2=fktable1 to enforce the 1:1? Or is this automatically enforced based within the database itself?

    Thanks for all the helpful answers thus far.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you define a foreign key in table A references table B's primary key, and a foreign key in table B that references table A's primary key then you are saying: "Every A must have a corresponding B and every B must have a corresponding A."

    Now this could be done in such a way as not to enforce a 1:1 relationship:

    Code:
    create table a (aid int primary key, bid int);
    create table b (bid int primary key, aid int);
    alter table a add constraint afk foreign key(bid) references b
    deferrable initially deferred;
    alter table b add constraint bfk foreign key(bid) references a
    deferrable initially deferred;
    insert into a values(1,1);
    insert into a values(2,1);
    insert into a values(3,1);
    insert into b values(1,1);
    commit;
    But, as I said earlier, this 2 constraints are not defining the same relationship - e.g. a(2) references b(1), but b(1) references a(1) not a(2).

    To make them define the same relationship they would need to involve the same columns in both cases:
    Code:
    create table a (aid int primary key);
    create table b (bid int primary key);
    alter table a add constraint afk foreign key(aid) references b
    deferrable initially deferred;
    alter table b add constraint bfk foreign key(bid) references a
    deferrable initially deferred;
    Now what happens if we insert rows into A without a corresponding row in B?
    Code:
    insert into a values(1);
    insert into a values(2);
    insert into a values(3);
    insert into b values(1);
    commit;
    
    ORA-02291: integrity constraint (DESDIR.AFK) violated - parent key not found
    The only way to set up the data with this "bi-directional" relationship is that for every a.aid there is a corresponding b.bid and vice versa.

    That is how it enforces a mandatory 1:1 relationship.

    Come to think of it, didn't rajiravi just say all that and didn't you just say you understood?

    So to deal with your questions, such as I understand them:

    1) Yes it does, no they could not. Of course a foreign key can't point to a primary key that doesn't exist - how would that be different from not having a foreign key!?

    2) Yes, it would impose a burden on the users - unless you created a view that performed the join, and told the users to use that.

    My question would be: what do you think would be the point of defining these 2 Siamese Twin tables when a single table would achieve the same end so much more simply?

  9. #9
    Join Date
    Mar 2006
    Posts
    43
    Wonderful answer, thanks a lot.

    I'm not sure when this case might be more useful than just a single table (sometimes my questions may seem like I know almost nothing, which is pretty much the case ), but I've learned a lot about this whole topic from reading all these answers.

    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
  •