0

I have a table to store the customer details. I need to add a new column to give these customers or new customers a unique auto increment ID to identify them and also need the primary key to keep the count of the customers.

I already have primary key AI to display the number of customers. I Googled and learned that only primary key can be auto incremented. So how can I achieve this?

I need the customer ID to be of only number of about 5-10 digits. Something like this: uid = 232100001. It should start from one number and auto increment when the primary key is auto incremented.

Ivan Tyler
  • 23
  • 5
  • 2
    "*I already have primary key AI to display the number of customers*" - There is no guarantee that AI will always ensure that the values are continuous; also you may `delete` some of it later creating gaps. So you should really not depend on the `MAX()` to get total count of customers in the table. – Madhur Bhaiya Sep 14 '19 at 08:10
  • 1
    Why not just derive it from the primary key value e.g. `id + 232100000`? – Nick Sep 14 '19 at 08:10
  • 1
    Don't derive it from the PRIMARY KEY. Just have a table of customers, and a table of activity – Strawberry Sep 14 '19 at 08:18

1 Answers1

0

I am using TRIGGERs to keep track of the count of such things

More infos you can find here

For example you can create another table with the name counters on which with the trigger will update the registered_users column when a user is added or deleted.

For the IDs of the users you just make the primary key start from a specific value. An answer for this is given on another question here.

Memos Electron
  • 660
  • 5
  • 26