#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    85
    Rep Power
    8

    1 foreign key references 2 primary keys


    Code:
    table1
    ID (PRIMARY)
    
    table2
    ID (PRIMARY)
    
    table3
    IDpar(FOREIGN KEY)
    I have 2 IDs from 2 different tables (table1 and table2) and they are primary keys. In 3. table (table3) I have IDpar which is foreign key and references ID in table1 and table2.

    if I do this:
    Code:
    FOREIGN KEY (IDpar) REFERENCES table1(ID)
            ON UPDATE CASCADE
            ON DELETE CASCADE
    FOREIGN KEY (IDpar) REFERENCES table2(ID)
            ON UPDATE CASCADE
            ON DELETE CASCADE
    How will database know which value in column IDpar(table3) comes from ID(table1) or ID(table2). ID from both tables can have the same values because they are auto increment field.

    Code:
    ID(table1)
    1
    2
    
    ID(table2)
    1
    2
    
    IDpar(table3)
    1 (ID table1)
    1 (ID table2)
    2 (ID table1)
    2 (ID table2)
    Now if I update ID(table1) from 1 to 3, then in table3 IDpar will be updated everywhere from 1 to 3, right?
    That should not happen because one value in IDpar comes from table1 and other from table2.

    Could IDpar be primary key and IDs foreign? maybe that would solve the problem?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by dada1
    How will database know which value in column IDpar(table3) comes from ID(table1) or ID(table2).
    it will not and cannot

    the way you have defined things, the table 3 FK value must exist in both table1 PK and table 2 PK simultaneously

    perhaps this is not what you intended

    if you would use actual table names, so that we can get some meaning (semantics), perhaps we could help further

    but with "table1" "table2" "table3" there is no way for us to guess what you're trying to do


    Originally Posted by dada1
    Now if I update ID(table1) from 1 to 3, then in table3 IDpar will be updated everywhere from 1 to 3, right?
    no, sorry, that never happens

    ~you~ have to do the update

    PKs do not automatically propagate to FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    85
    Rep Power
    8
    Code:
    racing_pair
    ID (PRIMARY)
    
    breeding_pair
    ID (PRIMARY)
    
    brood
    IDpar(FOREIGN KEY)

    Code:
    ID(racing_pair)
    1
    2
    
    ID(breeding_pair)
    1
    2
    
    IDpar(brood)
    1 (ID racing_pair)
    1 (ID breeding_pair)
    2 (ID racing_pair)
    2 (ID breeding_pair)
    tables represents racing and breeding pair of pigeons and brood is their young pigeon. And I'm using IDpar to identify which brood belongs to which pair.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    sorry, i still don't get it

    how can your tables only have id columns and nothing else?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    85
    Rep Power
    8
    There are other columns, but I think they are irrelevant for this situation cause they are mainly varchar columns
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    i'm afraid i just don't understand your design

    for one thing, it looks like only pairs of birds are identified, and the individual birds are not

    makes no sense to me, sorry

    and what is "IDpar" all about? is it something different from racing and breeding?

    i hope you will find someone else more knowledgeable in birds than i am who can answer your question

    what was your question again anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    85
    Rep Power
    8
    Thanks for your effort, I'll try to ask somewhere else.

IMN logo majestic logo threadwatch logo seochat tools logo