By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,649 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,649 IT Pros & Developers. It's quick & easy.

How to change Primary Key value in existing database?

P: 12
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
Mar 23 '07 #1
Share this Question
Share on Google+
2 Replies


P: 12
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";Text 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.
Mar 23 '07 #2

Rabbit
Expert Mod 10K+
P: 12,374
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.
Mar 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.