Update table to replace primary key

[redsanders]

My database tracks all of the staff development sessions provided for the past ten years, thus we have thousands of records. Our school district wants all departments to move away from using an employee's Social Security number to a number assigned by the district. Each employee has been assigned a unique six digit number.

In my staff development database, the employees data (Social Security number, campus, job description, etc.) are in a table, "EmployeesTbl", with the primary key being the Social Security Number. Classes data are in a table, "ClassesTbl" with some of the fields being ClassNum (an autonumber), class name, class description, etc., with the ClassNum being the primary key
in this table.

A third table, "AttendTbl", links the two and shows each class (staff development session) each employee has taken. This table has a a foreign key, "SocNum", that links it to the EmployeesTbl, and another foreign key, "ClassNum", that links it to the ClassesTbl.

I have added the EmpNum field to the EmployeesTbl, and all of the the district assigned employees' numbers have been entered. I have also added this field to the AttendTbl. Is there a means of using a query to fill in the employees number in this table. Is so, I'll then change the primary key in the EmployeesTbl to be the employee number and the foreign key in the AttendTbl to be the same. If not, we face the task of having to key in 18,000 records!

Thanks for any help,
R. Sanders

 

[rainman89]

you could try an append query that will add the empNum field in the employees table to the empNum in the attend table. is that what u are asking?

 

[boblarson]

It wouldn't be an append query, but an UPDATE query.

 

[rainman89]

haha yea! thats what i meant

 

[namliam]

Why change the PK tho?? Social Security Number should be consistant, unique and be never changing all the pre-reqs for a PK.

Unless there is a real need for it simply dont change the PK, just add the field you want to add and leave the PK be.

 

[boblarson]

Namliam:

What they were saying is that their primary key was a person's Social Security Number (which in the US is a common thing, but very bad from a privacy standpoint and ease of identity theft thing) so they want to change the primary key from that to something else. So, they MUST remove the field from their database for privacy issues, and they have to add some sort of key, so they can't just leave the pk alone.

 

[namliam]

Well living in the NL doesnt give me day to day updates on new ledgislation in the US. Then again why not leave the SSN field as is but simply update that??

With referential integrety a cascade update should be triggered that updates the referenced table, without the need to do a redesign to implement a new field.

 

[rainman89]

If u rename the field in the table doesnt it update throughout the db? could do it like nailman says and just rename field then update? course that would be the same as creating a new field and updating that!

 

[redsanders]

Your replies and assistance is much appreciated. Just returned to work this AM, so will try to implement suggestions. The comment about the security factor re the social security number is creating the problem. We have been using an 'SS number to sign the up for classes when they call or visit our department. Given the size of our district, it is not uncommon for us to receive a call or visit from a new employee. If they will not give us their SS number, which is increasingly occurring, then we cannot add them to the database if the SS number is the primary key! They are required to provide their employee number. Will post again as soon as I can see if I can implement your suggestions.

 

[namliam]

Long live meaningless primary keys... I am growing to love it more and more to allways implement meaningless primary keys !

 

[rainman89]

Hear Hear!!

 

[redsanders]

Update query worked as advised. Very much appreciated!

I see that a meaningless PK has merits. However, I need a little more time to see how this would work for me. Our district is primarily hispanic - and it is not uncommon to find two or more folks with the same name, and in some cases, the same middle initial! So as to provide them assistance in a timely manner, we bring up their records by asking for something unique - which has been their SS number. I question the wisdom of expecting our employees to memorize a unique/meaningless number - and to remember that number when calling for assistance. Again, maybe I am not seeing an easier solution that some of you might have in mind. To repeat, assistance of much value to us.

 

[boblarson]

Actually, you don't need to work with or memorize a meaningless key. You should USE it, but you can also have the meaningful number in the database and set it to no duplicates which will keep the employee number from being added more than once.

 

[rainman89]

your primary key should be something like an autonumber that will be unique to each entry. u could use their employee # like bob said and base all your queries and such off of that. the PK would just sit there!

 

[The_Doc_Man]

Ah, but the key here ISN'T meaningless. It is an employee number. There is a fine line here, but a truly meaningless key isn't used for ANYTHING except linkages. This might actually show up on a report as an employee number. It might also get used on timesheets and paychecks. Not at all meaningless.

The correct way to do this (for future reference) is

1. Add the new field to each affected table that used the particular key either as PK or FK.

2. From whatever source you have for same, update the new field in all tables so that the new field now contains the unique ID number that will become your new PK or FK.

3. Print your relationships page.

4. Remove all relationships based on the (leaving) key.

5. Open the main table in design mode. Change the primary key designation from the old key to the new one. Save the table. It is unlikely that other tables will have the same primary key - though it could participate as a member of a compound primary key. In all cases where that happens, again redesignate the key membership to remove the old one in favor of the new one.

6. Re-establish all relationships using the new PK. (That's why you printed this out before.) Note that you could not have built a secondary relationship earlier because you can only build the "one" side of a one/many using the PK.

7. Now go back and remove the (no longer used) key fields from all tables that have no further use for it. If the main table still uses the old key field as legitimate data, don't remove it from there. Basically, any place the old key was FK, remove it.

 

[redsanders]

Thanks again guys - and Doc Man - you correctly anticipated the gravity of the employee number, and almost exactly described the steps I've already taken to update my database. Having to update a number of forms that displayed SS num, but just cleanup work. In other settings I can see the merit of an arbitrary PK.