Since you show an Ohio address, your site falls under the rules of the USA Privacy Act of 1974 and all subsequent amendments thereto. Unless you are a government site (state or federal), it may be illegal for you to use SSN as primary key. If your company has a legal department, that would be a good question to ask them. You might point them to start with US Code, Title 5, near section 552, and Title 18, near section 1030. But that isn't the only place those issues come up. Just two places that I KNOW reference the issue of sensitive personal data.
Yes, I know that insurance companies use your SSN all the time - but many of them cheat, while others get away with it because of the tax implications, particularly if they are subsidized by some form of government funding. THEY call SSN your "Taxpayer ID" - which oh so conveniently matches your SSN. Or they add a digit to it so that it isn't your SSN any more. But EVERYONE knows which digit to remove to GET your SSN. I get away with using SSN here because my site is military (US Navy). My primary DB relates to military personnel. I also run the host system for that database at security level C2 in order to have that info legally on line. Otherwise, I couldn't use it either.
As to whether you should continue to use SSN as a primary key, I cannot advise against it from a techie standpoint. SSN, because it is relatively short and is supposed to be unique, meets the requirements for a good primary key. If you take out the punctuation, CLng(SSN) can even fit into a long integer because the largest SSN currently possible is still less than 1 billion. Long integers can, of course, comfortably hold over 2 billion. So in that sense, SSN and a long integer autonumber are almost equal in their utility as keys.
Some purists suggest that primary keys must not also have meaning. I.e. because SSN has a meaning outside the context of your DB, it should not be a primary key. I happen to not be of that viewpoint. Sometimes it makes more sense to use an existing unique value as a key rather than synthesize another field with no particular meaning to act as a key.
Switching your tables to an autonumber field is certainly OK, but you would run into a massive update issue when you converted the records and relationships. Still, it COULD be done.
First, add the autonumber field to your table but DON'T let it be the primary key (yet).
Next, for every table that references SSN as a foreign key, add a long integer field to that table. Since here the key relation will be foreign, this new field is NOT an autonumber. At this point, the parent and child tables are still related through SSN.
Now write a query that copies the autonumber field value from the parent table to the corresponding new field in each child table. Since the existing SSN relationship is still intact, you can do this pretty easily.
Now make the autonumber field the new foreign key for the child table. Change all relationships with that table if they involved SSN. Immediately after all relationships are fixed for each table, DELETE the SSN field from the child table. Because at that point you have a non-key field that does not depend on the child table's primary key, which is one of the things that normalization rules say you shouldn't have.
Finally, change the primary key of the parent table from SSN to the autonumber field.
As to how we approach the issue of a change of primary key in a context of referential integrity, here is what I have found to be effective. I wrote a macro to run some code to do this. The code asks questions with input boxes to get the old and new keys for the "rename" operation. It then creates little append, update, and delete queries on the fly. The code opens these SQL queries as recordsets. Opening an action query as a recordset executes the query, of course.
First, open a SELECT query for the record to be changed. Copy all the data from the record. Make it a SnapShot record or something that clearly doesn't diddle with locks. Or, just close the recordset after you have copied what you wanted.
Next, open an APPEND query that copies everything else but the primary key to the same table. For the primary key, load the new value. Close the recordset.
Next, for each table containing foreign-key references, open an UPDATE query that changes the value in the primary key field for all records that match the old value of the primary key.
Finally, open a DELETE query on the parent table, deleting the record with the old value of the primary key.
Tedious, but if you break it up into parts, it isn't so bad.
