sql - How to make string auto increment

Question

I got 2 different table:

Table1

ID
AK001 
AK001 
AK001 

(above should be AK001, AK002, AK003 and so on....)

Table2

LastNum
 1

Table 1: update table1 set ID = xx + RIGHT('....'+()) and so on

Table 2: UPDATE table2 SET LastNum = LastNum + 1 (this works, but when I enter wrong detail when at create new form ,not yet save, its also give me +1)

my question is how to auto increment and update in Table 1 after Table 2 have been updated? Because what im facing is the table1 keep give me the same number when table2 is updated. Please help!

Answer 1

You should consider using INT as primary key and not a varchar. Then use that field as foreign key.

Here is a detailed answer on why not to use VARCHAR as any type of key.

Keep storing yout store ID as varchar, just add new field on both tables, on the main one make sure to be IDENTITY, it would auto-increment.

There are many opinions on the subject about using VARCHAR as primary key. You should carefully analize the problem you are trying to solve. And ofcourse, maybe you just can't change anything and work with what already you have. On that case you need to use some work around as a trigger as suggested by @sgeddes.