How to change Primary Key value in existing database?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • VictorS
    New Member
    • Mar 2007
    • 12

    How to change Primary Key value in existing database?

    Hello everybody!
    I’ve inherited a moderate size Employee database.
    Main Employees table has a SSN as a primary key, but it is a “Text” data type.
    I’m now told to delete social security numbers due to security reasons, change primary key to preferably autonumber or other unique value, but still keep last 4 digits as a new field.

    tblMain structure
    SSN;Text; primary key
    LastName; Text
    FirstName;Text

    This table is in one to many relationship to 10 other tables.
    Recently I selected “Cascade Update Related Fields” in order to update some incorrect numbers.

    Here are my thoughts:
    Using Right([SSN],4) I will create a new field in tblMain to track last 4 digits.
    This field unfortunately has to be a “Text” in order to run append query.
    As for changing SSN field to autonumber- no solution yet.
    I could manually update SSN to unique text using first 3-4 letters of the name, but how to make users to apply same technique when entering new employee?

    Any help is greatly appreciated!

    Victor
  • VictorS
    New Member
    • Mar 2007
    • 12

    #2
    Correcting myself:
    I cannot use autonuber in tblMain- it will allow duplicate entry.
    Here is what I did so far:
    Used Update quert to create field "Last4SSN" using expression
    Right([SSN],4)
    Used Update query to create field "NewId";Tex t data using expression
    Left([LastName],1) & Mid([FirstName],1,1) & Right([Last4SSN],4)
    "NewId" field appears to be unique.
    Next step- manually update SSN field to new values of "NewId"
    I guess I have keep fields name as is (SSN) in order to make everything work.

    Comment

    • Rabbit
      Recognized Expert MVP
      • Jan 2007
      • 12517

      #3
      Is this a make table query? I assume it is.

      The problem with your expression is that with enough people you're eventually going to have 2 people with the same ID.

      What you can do is use a multi column primary key. After the table is made, go into table design view and set the last name, first name, and last 4 SSN as the primary keys.

      Comment

      Working...