Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Change primary key (2000)

    My table (tblCourses) now has a primary key of ID (the standard Access autonumber). The table has another field called CourseNbr, and I want to make the latter field the primary key. In design view I selected the chosen field and clicked on the Primary Key button. So far, so good. But then I tried to save the table, and I got an error message. Message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationships. Change the data in the field or fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again."

    I don't get it. The CourseNbr field has no duplicate entries--I suppose someone could enter a duplicate by mistake, but every one of our courses has a distinct number.

    So I tried to just change the ID field name to "CourseID." Sounds easy enough, but that attempt got me the same error message. How could changing the name of this autonumber field bring about duplicate values?
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Change primary key (2000)

    First of all, why bother to change what field is the primary key? You can still use the CourseNbr field as your primary means of identifying a course, and the fact that Access uses a different # as Primary Key is insignificant.

    Assuming that CourseNbr has been defined as having a unique index, your biggest problem is relationships with other tables. Other tables connect to tblCourses via the current primary key field (that is, ID). Change which field is primary key in tblCourses DOESN'T change the data in these other tables!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Change primary key (2000)

    Doh! <img src=/S/doh.gif border=0 alt=doh width=15 height=15> You're quite right! Thanks for helping me understand!

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change primary key (2000)

    It will be worthwhile though to make sure that your CourseNbr field is Indexed to Yes(No Duplicates) !

    HTH

    Peter

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Change primary key (2000)

    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
  •