Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Question Unanswered: Two relationships with one direction from a table to other table

    When I try to design relation database using MSSQL Server 2008, I find that the MSSQL Server 2008 allow designer can create two relationships with one direction from a table to other table (for example, I have two table A an B, I can create two relationship from table A to table B with key from table A map with some field in table B). Please let me know in the real-world, do we need some relationships between two tables like that?

    Many Thanks,
    John.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - I am not certain I understand. Please could you either post the DDL or an image of the design?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think what you are talking about is you want a multi-column foreign key? Or do you mean 2 separate foreign keys? The typical solution would be a multi column foreign key, meaning both/all columns have to exist in a single row of the parent table. I can't, at the moment, think of a case where I have seen multiple foreign keys to the same parent table.
    Dave

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dthung View Post
    Please let me know in the real-world, do we need some relationships between two tables like that?
    here's an example...
    Code:
    CREATE TABLE teams
    ( abbr CHAR(3) NOT NULL PRIMARY KEY 
    , name VARCHAR(99) NOT NULL
    );
    INSERT INTO teams VALUES
     ( 'BUF' , 'Buffalo Bills' )
    ,( 'ATL' , 'Atlanta Falcons' )
    ,( 'WAS' , 'Washington Aboriginal Americans' )
    ;
    CREATE TABLE games
    ( nom SMALLINT NOT NULL PRIMARY KEY 
    , scheduled DATETIME NOT NULL
    , hometeam CHAR(3) NOT NULL REFERENCES teams (abbr)
    , awayteam CHAR(3) NOT NULL REFERENCES teams (abbr)
    , homescore SMALLINT
    , awayscore SMALLINT 
    );
    INSERT INTO cames VALUES
     ( 23, '2010-09-09 09:37', 'BUF' , 'WAS',   35,   14 )
    ,( 24, '2010-09-16 21:00', 'WAS' , 'ATL', NULL, NULL )
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oooh that's nice. The only example I could think of off-hand was repeating groups.

  6. #6
    Join Date
    Jun 2010
    Posts
    2

    More description about my question

    Tnks somuch for quickly reply and support,
    My situation is as the picture in attachment file

    Tnks,
    John.
    Attached Thumbnails Attached Thumbnails 2 Relationship.png  

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case, Rudy's example is a legitimate one.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is it even possible to establish a "dual citizenship" type of DRI? What would be the definition of these FK's?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov View Post
    What would be the definition of these FK's?
    post #4, the part in blue

    unless i misunderstand your question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    But this definition does not prevent an entry where Buffalo Bills play themselves. So how would you define such a key?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov View Post
    So how would you define such a key?
    how would i? i wouldn't



    how would one? with a CHECK constraint, i think


    is it me or was today exceptionally hot outside?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rdjabarov View Post
    But this definition does not prevent an entry where Buffalo Bills play themselves. So how would you define such a key?
    ALTER TABLE games
    ADD CONSTRAINT ck1
    CHECK (hometeam <> awayteam);

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, that's cheating! I asked about the definition of an FK, not how it can be done using other means. But I wouldn't even structure the tables that way for this particular task. GAMES table should have at the very least a child table, where you'd put home and guest teams as records. Let alone other info about the game stored in other child tables, like referees, the actual field, teams' roster as a child to the first child, etc.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and as you all can see, - I am bored out of my mind, can't believe I am doing this now...Where's that address to this new bar someone told be about during lunch? Aggies!!!! That's it! And the happy hour is all night!
    Last edited by rdjabarov; 06-25-10 at 18:49. Reason: Here it is!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rdjabarov View Post
    Hey, that's cheating! I asked about the definition of an FK, not how it can be done using other means.
    A foreign key is just an inclusion dependency constraint that references a candidate key in another table. If the constraint you want to implement is something different to an inclusion dependency then it's not a foreign key. So your question doesn't make much sense.

Tags for this Thread

Posting Permissions

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