6

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

TuK
  • 3,556
  • 4
  • 24
  • 24
  • Not a MySQL expert at all, but you might want to check if it supports sequences. – Limey Jun 22 '11 at 18:03
  • What's the purpose? It sure looks like you're violating a pretty basic rule of database design. A column should only contain a single piece of information. Concatenating alpha-numerics like that is often a sign that this rule of normalization is being violated. Not always, but usually. – Tom H Jun 22 '11 at 18:07

3 Answers3

12

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),
    ...
hughes
  • 5,595
  • 3
  • 39
  • 55
  • You need only one letter as prefix i.e R123 so you should use CHAR(1) instead of CHAR(30) to save space. If you needed two letters as prefix i.e. RG123 then you should have used CHAR(2). – Naeem Ul Wahhab Dec 31 '12 at 20:44
  • 1
    I'd avoid the compound key if the leading character will be constant. Identifying a record with a pair of variables gets rather messy down the line. – Brad Koch Mar 07 '13 at 06:27
3

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

a1ex07
  • 36,826
  • 12
  • 90
  • 103
2

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)
);
Gidon Wise
  • 1,896
  • 1
  • 11
  • 11