How to make a primary key start with a specific letter?

Question

Here I am using MySQL and I want my primary key to start with a letter, like D000. Then everytime I enter a new record the primary key auto increments like so:

D001 D002 D003.

How can I do this?

Answer 1

You can't AUTO_INCREMENT a column whose type is VARCHAR.

What you could do is make it BIGINT and AUTO_INCREMENT, and whenever you need it as String, you can prepend it with your letter 'D' like:

Long dbKey = ...;
String key = "D" + dbKey;

You could create a stored procedure for this to set an "auto-incremented" string as the default value for this column, but it just doesn't worth the hassle. Plus working with numbers is always faster and more efficient than working with strings.

Answer 2

Not sure why you need it but you can add the D AFTER you fetched the data (String id = "D" + autoIncId;).

You can't insert a string or anything in an autoincrement field and I can't see anyway this can be useful (all the recorde will have a D, so no one has).

If you want to declare a row default, you can add a boolean column named DEFAULT.

while(rs.next()){
    String id = rs.getBoolean("DEFAULT")?"D":"ND";
    id+=rs.getLong(1);
}

EDIT

As per your comment I understand that you want to select the max ID and add 1 to it. Then it's ok to use an autoincrement field in your DB and it must be a number type (INTEGER, BIGINT...).

Please FORGET to add the "D" to your primary key, it will simply not going to work as you want. The autoincrement takes the last inserted ID and adds 1 to it. If your last id is "D3" adding 1 has the same meaning as adding 4 to "apple". You are using different types.

There is no way for SQL or any other programming language to understand that if you add 1 to "D3" it should become "D4". What you need to do is get rid of that D (which purpose I still don't understand).

Answer 3

I'm not sure whether I get your question right, but shouldn't the following work?

CREATE TRIGGER myTrigger
  BEFORE INSERT
  ON myTable
  FOR EACH ROW
BEGIN
  SET NEW.myCustomId = COALESCE('D', RPAD('0',3,NEW.id)); 
END

for this case you NEED a "normal" primary key column..

Answer 4

Two ideas.

• (Useless IMHO) I think Maria DB has virtual columns, though MySQL I think not. But you have views. So you could make a normal INT, AUTOINCREMENT and in the view have a calculated column concatting your key.

• One can use different number ranges for different tables.

  ALTER TABLE debtors   AUTO_INCREMENT=10000;
  ALTER TABLE creditors AUTO_INCREMENT=30000;
  ALTER TABLE guests    AUTO_INCREMENT=50000;
  

  This admittedly is a lame solution, but might do. I think such a distinction might be what you are aiming at.

Answer 5

Yo may try to do this aberration at your own risk:

INSERT INTO table (id, a, b, c) 
      VALUES ( fn_get_key( LAST_INSERT_ID("table_name ") +1), "a", "b", "c");

Where fn_get_key is a function that will convert the number into your desired string AND will execute:

ALTER TABLE table_name AUTO_INCREMENT = start_value;

Anyway I do not recommend your approach. Numeric strings are faster and easier to sort. You could always create a view that transforms the ID or use logic o change from the "D001" key to "1". Foreing key and uniqness of ids enforcement will be harder and more expensive