Changing Primary Keys 1

[jeffshex]

I need any info on the best way to go through a database and change a whole bunch of primary keys.
The problem is that they are based off a SSN instead of an autonumber.
Is there any good way of doing this while maintaining relationships and the such?

Thanks!

 

[lameid]

I don't think changing indexes will break relationships. Just ensure that you keep an index on the SSN field when change the Primary Key.

After that you can change the relationships if need be. The catch will be changing all the queries that already join on the SSN Foreign Key.

While I realize the design is not ideal, it should work.

Are you experiencing difficulties with the design?

I have heard of SSN's repeating in large organizations but is rare and honestly should not happen.

 

[jeffshex]

No problems really.
The downside is that this is an old system and the person who put it together a long time ago didn't have the greatest database knowledge, so some tables don't even have a primary key. There is just the SSN on it.
The downside to that is data entry.

Put it in wrong once and it makes it's way through all the related tables. I just want to clear things up before I upsize this thing to SQL.
Either which way...I think it's going to be a mess because I really can't have any downtime with this system.

 

[lameid]

You could turn on cascade update on the existing relationships for SSN's. That would at least solve the immediate problem of what happens when the wrong SSN is entered.

Since downtime is an issue, I would add all the FK's to the approriate tables. Next write a series of update queries to update the FK based on the SSN. Put those in a macro.

Take a cut of your frontend at this point for later use. Modify a transitional frontend so that the screens populate both the new FK and SSN simulatenously using the afterupdate events. Since SSN is dominate at this point you might run your macro somewhat regularly until you can cut.

Once that is done and in production, I'd look at how many queries there are and take a hard look at whether it is worth manipulating them manually or programmatically. Be sure to change this in the cut frontend because you don't want to have to remove the update code later.

Once your queries are update. Try changing all the names of your links and replacing them with queries of the original name that select everything but the SSN FK's that are becoming defunct and of course make sure you turn off the 'feature' that cascade updates name changes first. Test. Once you are clean, relink the orignal tables (delete the renamed tables and queries first). Change back the autocorrect 'feature' if desired (having done most of my development in Access 97 before this feature it bites me more than saves me).

Once you modify your schema, you should be ready for production.

That is the cleanest process that comes to my mind at it is a lot of work at that.

Because SQL server supports cascade updates at least through triggers (I think SQL 2005 has a more native support) and ultimately comboboxes should be selecting the key with limit to list, I have to wonder if it is really worth it. Clearly there is value in doing it but what is the ROI? This is of course for your consideration only. I have a bad system myself that will be rewritten...

The alternate approach is to scrap the whole project and rewrite it in a new platform considering the existing schema and using queries to map the data. This may prove easier in the long run, especially if you are strong in SQL and the target Application platform (VB.NET / Access etc.)

One more thought... I personlly don't care for ADP's with native SQL support. I think you give up too much from abandoning the MDB frontend.

That's all I have.

 

[jeffshex]

Sorry this took so long, but it's coming close to time to do this since I'm restructuring it all.

I'm curious as to how you would approach this:
"Next write a series of update queries to update the FK based on the SSN."

I'm going to move it into a whole new access db for the time being so everything is in place when we get our sql server.

 

[lameid]

Sorry update e-mail was lost in my mailbox.

I would probably write each query as I added the new FK to each table and then add that macro to the table. Join the table to the 1 side on SSN and then update the new FK value with the PK from the joined table.

I hope that answers your question. If not could you be more specific?

 

[jeffshex]

Thanks I got it.
I actually did it last weekend.
I put the PK on the main table and did an update query to populate the FK on the other tables.
Worked like a charm!

Thanks a ton!