Changing Primary Keys

[rosenn]

Hello all,

I am re-working an old database that 'grew up' the wrong way. It is a medical patient database and has 4 main tables. Primary key of main data table is Medical Record number (MR#), a user entered field (I know...). This links to 3 other tables: Diagnoses (indexed by MR# as well, 1-to-1 relationship with main table), Consults (indexed by a compound key of MR# and date, 1 to many relationship), and Procedures (indexed as well by MR# and date; 1 to many as well).

Here is my plan, and I want to know if I'm going to go about it the right way:

I will first delete the relationships between these tables.
I will then give the main table an autonumber field called PatientIndex, and each other table their own autonumber (DiagnosisIndex, ConsultIndex, ProcedureIndex). I will also give each of the other 'daughter' tables a PatientIndex field as well. I will populate the PatientIndex fields of the daughter records with a VBA routine that matches their MR# with the MR# in the main table, and then inserts the corresponding PatientIndex.

Now that each table has no relationships, and has all the right fields, I suspect Access will allow me to change the Keys for each table, and create new relationships, and it should all work cleanly. The goal is to have each table indexed by an autonumber field that is unrelated to user entry, and to link them by the Primary key.

Does this sound like the right approach? As to why do this if it works OK, it's a matter of maintenance. It's simply too buggy as it stands, and it's torturing me. Will my plan work without crashing the db?

 

[MStef]

I'm very doubting in it. It's better to catch on
high intensity then to change primary keys.
In any case take a copy of your MDB.

 

[Pat Hartman]

Make sure you back up the db before you start.

Yes with one exception.
You don't need VBA to populate the foreign keys. Just use an update query. Create a query that joins the child table to the parent table on the old pk, then update the foreign key field in the child record with the value of the new autonumber on the parent table.

Once the new foreign key fields are populated, delete the existing relationships. Then change the primary keys to the new autonumbers. Add a unique index for the MRNum on the parent table. This will enforce the business rule that MRNum must be unique for a patient. Go back to the relationships window and reestablish the relationships. If all is well, delete the old foreign keys and primary keys from the child records.

To ensure that everything is back in sync, you should probable run a few queries on the old db and make sure the results match the new db.

If you have any subforms or subreports, you will probably need to go in and set the master/child links manually. Make sure that everything works and wasn't broken by deleting the unnecessary fields.

 

[rosenn]

Thank you

Thank you both.

I actually did everything you said (with the exception of the update queries, which I'm still not nearly as facile with as I am with VBA, but that's no excuse), and it all worked smoothly.

Please let this be a lesson to anyone lucky enough to read this when they are young an impressionable - never make a user-entered field into a table's primary key, even if it makes sense to you. Primary keys are for the database, and you as programmer have the privilege of linking things with them. If you allow a primary key to be subject to the entry skills of your users, buggy things will happen. The best way to fix a database is to start it right from the beginning.

 

[The_Doc_Man]

With a grain of salt to be added so that newbies won't be confused...

A user-entered field, properly filtered, can indeed be a primary key if the content in question is absolutely guaranteed to be unique. You just have to be careful when you allow its entry. I agree that when the filtration process (if you prefer, VALIDATION process) is very complex, perhaps an autonumber is an easier idea.

But user-entered data makes perfect sense as all or part of a primary key if, for example, the same field was the primary key for the previous method of storing things, or if the same field IS a primary key elsewhere and is used in the same way in your table.

If this Medical Record Number you mentioned is unique, I see no particular data-oriented reason that it could not be a candidate for a primary key. Uniqueness and relevance are two strong reasons that something would become a candidate for a primary key.

Imagine an inventory table for which you have a part number and a supplier number as a two-part primary key. Meaning that you take the part number FROM THAT SUPPLIER plus YOUR number for the supplier and make it the PK for the product's description. As long as the supplier's part number is a primary key FOR THAT SUPPLIER, the compound key works based on user-entered data.

Consider (if you work for the government) your Social Security Number. For certain agencies this is a legal primary key, yet surely would be user-entered.

Don't get caught up on side issues. Look for what really counts - not that the data came from a user or a spreadsheet or a random number generator or the phase of the moon. Concentrate on whether the data in question will always be uniquely associated with the other data that came with it. If the association is now and forever unique, it is a candidate for a prime key.

 

[rosenn]

valid point

Thank you very much. That is all very valid.

My experience with these databases over the past few years is such that, while the MR# may be truly unique, people often write them down incorrectly, or enter them incorrectly, or try to enter them twice and get confused when Access won't allow them to continue entering the record, and then they change one digit so that it will be accepted. Then you get either duplicates or garbage.

Despite the lack of 'relevance' for an additional auto-number field, it's a great deal simpler for the database to be user-proof. Perhaps on a strict comparison of processing speed, the additional indexed field might slow you down a bit, when compared to a properly filtered user-entered PK, but I'm sticking with keeping the user out of the PK loop.

-Nelson