1 Reply Latest reply on Aug 18, 2009 3:12 AM by comment_1

    Calculated Value for Primary Key



      Calculated Value for Primary Key


      I have a problem.


      I have a Client table (client_id, client_name, address).

      I want the client_id is automatically assigned when a new client is registered based on following criteria:


         Format: X-00# where X is the first letter of client name and

                    # is the (total +1) number of clients with the same first letter as X.


         Scenario 1:

                My first client is John Doe.

                So the client_id for John Doe is J-001

         Scenario 2:

                I already have 3 clients (Jennifer, Dave and Jackson)

                So, John Doe's client_id is J-003


      How do I code this logic? Thanks.


        • 1. Re: Calculated Value for Primary Key

          The best way to do this is not to. A primary key should be completely indepedent of any attribute a record might have - not to mention other records (consider what would happen if a client is renamed, or deleted).


          You can easily append the first letter of client's name to an auto-entered serial number (running consecutively for ALL clients). It's also posible to calculate dynamically each client's ordinal number within their "group". But neither of these should be used as the primary key.