Auto serial reordering question
I currently have database with two tables. A client table and a policy table. One client is able to have multiple polices etc. I am using a masterkey field that uses auto serial numbering by one after each commit. That key sets a relationship with a policyIdKey in the policy table.
My question is, whenever I delete a client, I'd like the masterkey to renumber itself so that there won't be any gaps in the masterkey. But it also has to make sure to renumber the id key in the policy table occurrences as well.
Is is there an easy way to accomplish this without a series of loops and ifs?
thanks for any suggestions.