Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003

    Unanswered: ALTER TABLE - Primary Key

    I need to add an autonumber field (named UID) to be the primary key. The problem is that the table already has a Primary Key. Waht would be the ALTER TABLE query to drop the existing primary keys and create the new field as the primary key? Thanks in advance. Regards,

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    You don't need to resort to SQL "alter table", instead open the table designer and perform the changes there, you cannot remove an autonumber column and add a new autonumber column at the same time (you have to save changes inbetween
    the basic process is
    if you have any relationships identified on this key
    open the relationships dialogue, delete the reltationships (lines) between the column you want ot change and any child columns.
    close the relationships dialogue
    1) open the table designer with the table you want to modify
    2) remove the primary key indicator
    2) change the autonumber column type to long integer from autonumber
    3) add a new row 'UID' type long integer
    4) save the table, but don't leave the table designer
    5) change the column type for 'UID' to autonumber
    6) set UID as primary key
    7) save your changes
    8) open the relationship designer and re-establish any relationships
    it may seem longwinded but its very easy & quick to do.

    Just a thought, what's wrong with your exisitng autonumber primary key?

    Note if you have data already then you need to consider how you are going to re-establish the data integrity, or decide if you are going to delete everything and start from scratch. You could renumber everything but that would involve writing a fair bit of vba code or a series of well formed SQL update statementsto set the child column = to the new primary key

    Good luck.

  3. #3
    Join Date
    Mar 2003
    There are no existing autonumber fields on this database yet. What I want to do is to create an unique index for each record (it was lack of experience when I created the table structure). The problem is that I want to do this with a query (to be run through the program's code) so that all the users who uses this program get their database updated with the following release of the software.

Posting Permissions

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