Skip to content
Advertisement

How do I auto-increment a alphanumeric string primary key?

Let’s say I have table A with an alphanumeric string primary key. The code used to create the table and how the table looks are shown below.

I want to insert a new row into Table A and I don’t want to type out C000003 or C000004 every time. Is there a way to auto increment this?

I have thought about getting the latest id using

For splitting, I used SUBSTRING(ID, 2, 7). For joining back, I can use concat('C', ID + 1).

The issue is, if I add one to the numeric portion, it would give me 3 instead of 000003. Is there a way to save the 0’s?

I just need help with incrementing.

My current code is like this:

Any help appreciated

EDIT 1: If the numbers are less than 10 (ie one digit), I can manually add in 0’s to fill up the gaps. But if the number has 2 digits or more, I can’t do that so I’m thinking of a solution for this.

Advertisement

Answer

One word of advice: DON’T DO THIS! Using this SELECT MAX() + 1 approach is not safe under load, as soon as more than one user will be using your application, you WILL HAVE DUPLICATES – sooner or later.

The only viable solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

Now, every time you insert a row into Customer without specifying values for ID or CustomerID:

then SQL Server will automatically and safely increase your ID value, and CustomerID will contain values like C000001, C000002,…… and so on – automatically, safely, reliably, no duplicates.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement