0

Im having an issue attempting to increment unique ids for a table in MySQL. I have values I would like to keep as RX##### which I have labeled as a Primary key (prescriptionid).

Is there a way to increment the unique id when a new row is inserted?

prescriptionid
RX00001
RX00002
RX00003

CREATE TABLE prescription 
(   ID INT AUTO_INCREMENT,
prescriptionid AS 'RX' + RIGHT('00000' + CAST(ID AS CHAR(5)), 5) PERSISTED, 
.
.
PRIMARY KEY (prescriptionid),
.
.
...);
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Fern
  • 1
  • 1
    https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix – Elias Soares Jan 31 '21 at 06:03
  • Alslo see [generated column (MariaDB)](https://mariadb.com/kb/en/generated-columns/) / [generated columns MySQL](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) – danblack Jan 31 '21 at 06:21
  • 1
    You're confusing issues of data retrieval and display with those of data storage. Don't use this as your PK. – Strawberry Jan 31 '21 at 07:30

1 Answers1

0

You also need INDEX(Id) to heep auto_increment happy.

Caveat: It is possible that a number will be skipped. If this is not allowed by your business logic, then a different mechanism is needed?

Rick James
  • 135,179
  • 13
  • 127
  • 222