8

I am using a VARCHAR as my primary key. I want to auto increment it (base 62, lower/upper case, numbers), However, the below code fails (for obvious reasons):

CREATE TABLE IF NOT EXISTS `campaign` (
  `account_id` BIGINT(20) NOT NULL,
  `type` SMALLINT(5)  NOT NULL,
  `id` VARCHAR(16) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

however, this works:

CREATE TABLE IF NOT EXISTS `campaign` (
  `account_id` BIGINT(20) NOT NULL,
  `type` SMALLINT(5)  NOT NULL,
  `id` VARCHAR(16) NOT NULL PRIMARY KEY
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is the best way to keep track of incrementation of 'id' myself? (Since auto_increment doesn't work). Do i need to make another table that contains the current iteration of ID? Or is there a better way to do this?

EDIT: I want to clarify that I know that using INT is a auto_increment primary key is the logical way to go. This question is in response to some previous dialogue I saw. Thanks

Kenny Cason
  • 12,109
  • 11
  • 47
  • 72
  • Offtopic: Are you sure you want to use MyISAM? For a relational database, you need innoDB in MySQL to create foreign keys. – Frank Heikens Aug 11 '10 at 06:36
  • 1
    No, I am not sure at all. I've been using MySQL for quite a while now, however, I was told that MyISAM should be used most of the time and I just went with that :P – Kenny Cason Aug 11 '10 at 12:20

4 Answers4

7

you have to use an INT field
and translate it to whatever format you want at select time

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • That is not possible in my case as the ID is passed around as part of the URL. – Kenny Cason Aug 11 '10 at 06:05
  • Also, I am specifically looking for the best way to manually increment the ID. I know Bit.ly and possibly Tiny URL do something very similar. – Kenny Cason Aug 11 '10 at 06:06
  • @Kenny lol everything is possible for the programmer :) convert it before passing to the query. not a big deal. These url shorteners do it as I said. – Your Common Sense Aug 11 '10 at 06:10
  • Thanks, you don't HAVE to use INT field, some custom URL shorteners (for example) use a BIGINT as their auto incrementing primary key. But there are services that use VARCHAR as their primary Key and they Manually increment them. So my question is, is there an efficient way to "increment" a VARCHAR primary key. And I very much understand "everything is possible for the programmer" :) However, that also leads to many problems by implementing known, established practices! – Kenny Cason Aug 11 '10 at 06:32
  • @Kenny got an example of such a "problem"? What if your lame "varchar autoincrement" will lead to some problems too? Concurrency ones, for example – Your Common Sense Aug 11 '10 at 06:35
  • 6
    Well, I'm sorry that you think it's "lame" I'm just asking a question because of something I read in a discussion about using VARCHAR as a primary field, and was shocked to hear of a auto incrementing implementation of VARCHAR. As to "problems" with "everything is possible for the programmer", just because it is possible, doesn't mean it is right :P I don't think i need to provide an example. – Kenny Cason Aug 11 '10 at 12:27
  • @Kenny that was just answer to your "impossible". No examples needed. just quit that nonsense. – Your Common Sense Aug 11 '10 at 12:38
  • 2
    Agreed. natively `id` VARCHAR(16) NOT NULL AUTO_INCREMENT PRIMARY KEY is impossible. However, using VARCHAR as a primary key is not nonsense. I was merely asking how to "increment" it to guarantee uniqueness. And btw, I do use BIGINT for my ID, as it provides more than enough ID's for my implementation. "just quit that nonsense" <-- I really do appreciate you taking your time to answer, and I respect your high reputation on SO, but please have a little more tact, as my question was not impossible – Kenny Cason Aug 11 '10 at 14:36
  • 3
    Lol, I love to look back at posts like this and wonder what the h*** I was thinking. :) thanks again – Kenny Cason Jun 02 '11 at 17:41
0

Or just create a sequence and maintain the pk field using the sequence to generate the primary key value with nextval function. And if perf is an issue, use cache on sequence.

But as others have stated, this is sub-optimal, if your primary key contains a numbered sequence then it's better to use int and auto-increment. I don't see a use case where pk has to auto-increment but be a varchar data type, it doesn't make sense.

Jonas
  • 39
  • 1
0

example of a solution to your problem:

create a file with a unique number and then increment with a function.

the filename can be the prefix and the file binary content represent a number.

when you need a new id to the reg invoque the function

Example

    String generateID(string A_PREFIX){
        int id_value = parsetoInt(readFile(A_PREFIX).getLine())
        int return_id_value = id_value++
        return return_id_value
    }

where "A_PREFIX-" is the file name wich you use to generate the id for the field.

Gabriel Balbuena
  • 51
  • 1
  • 2
0

Assuming that for reasons external to the database, you do need that varchar column, and it needs to autoIncrement, then how about creating a trigger that grabs the existing autoIncrement value and uses Convert() to convert that value into a VarChar, dropping the VarChar into the field of interest. As mentioned in a previous answer, you could concatenate the table-name with the new varChar value, if there is some advantage to that.

ArtfulOne
  • 1
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 30 '23 at 16:08