Autoincrement of table id using string combination in database automatically

Question

We know that id of any table is of integer type and auto-increment in that table is fine. But my problem is that i want to increment a combination of string and integer. For example String : ABC is the starting code for the unique id.

First Id of my table should be primary key and

ie ABC10001, ABC10002,..... and so on.

Without using another key as auto-increment. Please do not use two type of unique key.

Answer 1

You cannot do it in one column, unless you specify a BEFORE INSERT trigger which would prefill it, leaving it wholly up to you to avoid any kind of race conditions the auto_increment handles on it's own. auto_increment can only be used on integer or floating-point columns.

In my opinion, 'requiring' the PK the have a specific format, and 'just having it auto increment' are mutually exclusive.

That doesn't mean you cannot 'fake' it on selects:

SELECT CONCAT('ABC1',LPAD(id,5,'0')) FROM tablename;

Answer 2

Alternatively to Wrikken's response, you can select the 'next' value from the db:

SELECT LPAD( max( substr( id, 5 ) ) +1, 4, '0' ) FROM tablename

And then use this value in your next insert statement.

Answer 3

It would be a bad idea to implement this. You will have to figure out the next value manaully and then you are subject to race conditions. This can cause serious data integrity problems that are impossible to fix if you don't get this exactly right. However, if you just want to display the number with the client name, this is possible but the numbers will not be in sequence for a client. (I'm using client as an example, I really don't know what your prefixes mean), so if ABC001, DEF002, ABC003 is acceptable then just concatenate to get the number from your automated real id.