CODEWITHSUNDEEP
mysqlsql
TuK
TuK

Reputation: 3556

MySql auto-incrementing Alpha-numeric primary key?

Is this possible in MySql ?? Can I have an auto-incrementing Primary Key, prefixed with a letter, something like R1234, R1235, R1236... ect ??

Upvotes: 6

Views: 15366

Answers (3)

a1ex07
a1ex07

Reputation: 37364

No. But for MyIsam tables you can create a multi-column index and put auto_increment field on secondary column, so you will have pretty much the same you are asking:

CREATE TABLE t1 (prefix CHAR(1) NOT NULL, id INT UNSIGNED AUTO_INCREMENT NOT NULL,  
..., PRIMARY KEY(prefix,id)) Engine = MyISAM;
INSERT INTO t1(prefix) VALUES ('a'),('a'),('b'),('b');
SELECT * FROM t1;
a  1
a  2
b  1
b  2

You can get more details from here Note: it's not going to work for INNODB engine

Upvotes: 3

hughes
hughes

Reputation: 5713

What you could do is store the key as two columns. A char prefix and an auto-incrementing int, both of which are grouped for the primary key.

CREATE TABLE myItems (
    id INT NOT NULL AUTO_INCREMENT,
    prefix CHAR(30) NOT NULL,
    PRIMARY KEY (id, prefix),
    ...

Upvotes: 12

Gidon Wise
Gidon Wise

Reputation: 1916

you can do it with two fields like this. but you can't do it with one field to my knowledge.

create table foo (
  code char,
  id int unsigned not null auto_increment
  primary key(id,code)
);

Upvotes: 2

Related Questions