Results 1 to 6 of 6
  1. #1
    elektrisk is offline Novice
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Posts
    4

    Multiple primary keys


    I'm trying to create a relationship between a student and a class table using a third table called an "attendanceTable". I wanna add two attributes to the attendance table; one called "studentKey" and one called "classKey". Am I mistaken in assuming that these both would need to be primary keys, since Access will only allow one primary key as well as one AutoNumber? I'm using Access 2007.

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hi elektrisk,

    Sorry but, yes, you are mistaken. "studentKey" and "classKey" would/could be foreign keys if you wish. There's no special way to designate them as a foreign key, other than using the fields as part of a relationship.

    Access will only allow one primary key, but it is possible to designate multiple fields as the primary key for a table.

    Cheers,

  3. #3
    elektrisk is offline Novice
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Posts
    4
    Quote Originally Posted by ConneXionLost View Post
    Hi elektrisk,

    Sorry but, yes, you are mistaken. "studentKey" and "classKey" would/could be foreign keys if you wish. There's no special way to designate them as a foreign key, other than using the fields as part of a relationship.

    Access will only allow one primary key, but it is possible to designate multiple fields as the primary key for a table.

    Cheers,
    Are you saying that, although Access technically only allows you to designate one field as the primary key, you could technically have more than one in the relationship?

  4. #4
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    For each student, there are multiple classes. For each class, there are multiple students.

    Classes and students have a many-to-many relationship. Therefore, join these tables with a "junction" attendance table. See the attached jpg file.

  5. #5
    elektrisk is offline Novice
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Posts
    4
    Thank you. You solved my problem.

  6. #6
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    What I am not saying is:

    Quote Originally Posted by elektrisk View Post
    ... although Access technically only allows you to designate one field as the primary key, ...?
    This is NOT true. You can designate more than one field as the primary key. It is called a compound key.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 09-06-2009, 07:27 PM
  2. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  3. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11:33 AM
  4. Are text primary keys less efficient than autonumbers?
    By bar tomas in forum Database Design
    Replies: 4
    Last Post: 05-11-2009, 09:37 AM

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
  •  
Other Forums: Microsoft Office Forums