Auto-increment for Primary Key

Forum Moderators: open

Message Too Old, No Replies

Auto-increment for Primary Key

humannaturesg

5:00 am on Oct 3, 2007 (gmt 0)

Hi All experts,

I am a newbie to MySql, thus need your advice to my problem I am facing now.

What I am trying to achive is to set my Primary Key to be auto incremental. I knew that it is a simple task. The challanging part is the data type must be "varchar" not "int". (DBA constraints)

I have no idea how to accomplish an auto-increment equivalent for varchar. Anyone can advise?

Thanks in advance

aspdaddy

4:09 pm on Oct 3, 2007 (gmt 0)

You can use something like this to get around it, but unless all your IDs can convert to INTs it will fail. If you can guarantee it wont fail then tell the DBA they using the wrong datatype!

Declare @i as INT
Declare @s as Varchar(15)
BEGIN TRANSACTION
Set @i = (SELECT MAX(ID) from tbl)
Set @i=@i+1
Set @s = (SELECT Convert(Varchar(15),@i)
INSERT INTO tbl (ID)
VALUES (@s)
END TRANSACTION

mjwalshe

3:50 pm on Oct 17, 2007 (gmt 0)

id tell your boss you need a new DBA

why on earth would an auto incremnet be a varchar

Demaestro

5:55 pm on Oct 17, 2007 (gmt 0)

humannaturesg,

Question...

Do you need to increment the alpha characters?
...(ie "a" increments to "b"..."m" increments to "n"....."z" increments to "a")

Or do you need to retain some product text that gets appended onto a 6 digit number

...(ie "widget000123" increments to "widget000124"...."superWidget000321" increments to "superWidget000322")