2 Replies Latest reply on Jan 27, 2015 8:06 AM by WBSimon

    Auto serial reordering question



      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. 



        • 1. Re: Auto serial reordering question

          That's a complication that, if an error is introduced, could seriously scramble your database. Thus, it is best NOT to do such an update of the field used as your primary key in a relationship. What problem would such a change solve for you?

          And I'd think that you'd need the field that auto-enters the serial number in your Client table should auto-enter that value on Record Creation rather than commit to more smoothly facilitate linking a new policy record to a new client record that might not yet have been committed.

          • 2. Re: Auto serial reordering question

            Best thing is not to worry about gaps in the masterkey. Unique ID's are just that. Unique. Once you delete the record, you delete the unique masterkey. It's gone.