creating a primary key format

Question

how do i create a primary key for an account with a format like this:

ABC-123

ABC-124

ABC-125

another example:

BCA-111

BCA-112

BCA-113

and so on.

by the way im using mysql.is it possible to do auto increment when using this format?

Answer 1

CREATE TABLE foo (
  prefix CHAR(3) NOT NULL,
  num    TINYINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (prefix, num)
) ENGINE=MyISAM;

This doesn't store both parts of the key format you described into a single column, but it does support auto-increment. And you can get the format out like this:

SELECT CONCAT_WS('-', prefix, num) AS pkey ... FROM foo;

Note that InnoDB doesn't support auto-increment compound primary keys.

For more details read Using AUTO_INCREMENT.