Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Exclamation Foreign Keys as Primary Key

    Hello,
    Can I use 2 foreign keys from different tables as a primary key for a third table...?

    Leave(leave_type, days,...)

    Staff(ID, name,....)

    LeaveBalance(LeaveID, balanceDays....)


    I need LeaveID to be the primary key of LeaveBalance, where LeaveID comprises of leave_type from Leave and ID from Staff....

    Thanks...

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You can have a multi-column primary key, if that's what you are asking.

    LeaveBalance(Staff_Id, Leave_Type, balanceDays....)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    Can't we have composite attributes as primary keys...?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think that may be specific to the platform you're using. I know you can't in Access or Sql Server.

    Of course, every time I use "I know you can't..." somebody else jumps in with
    "Sure you can..."
    Last edited by RedNeckGeek; 03-01-07 at 16:42.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by techno_brains
    Can I use 2 foreign keys from different tables as a primary key for a third table...?
    of course, yes you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What's the difference between having "composite attributes as primary keys" and a standard multi-column (composite) key?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Hold on a second here... If I'm reading this right, "LeaveID comprises of leave_type from Leave and ID from Staff" does that mean that, say, type is a letter and ID is a number so leaveID is going to be X123 or Y456?

    You can certainly make that a primary key, but most DBMS's won't let you set up a foreign key relationship that way.

    What you *could* do is set up a table LeaveBalanceTbl with two columns, type and ID as the primary key. Then set up a view LeaveBalance that concatenates those columns into one. That would probably work.

Posting Permissions

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