Menu
Is this possible in MySql ?? Can I have an auto-incrementing Primary Key, prefixed with a letter, something like R1234, R1235, R1236... ect ??
393
One of the important tasks while creating a table is setting the Primary Key. The Auto Increment feature allows you to set the MySQL Auto Increment Primary Key field. This automatically generates a sequence of unique numbers whenever a new row of data is inserted into the table.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
To change a primary key to auto_increment, you can use MODIFY command. Let us first create a table. Look at the above sample output, StudentId column has been changed to auto_increment.
If you want to avoid writing sql, you can also do it in MySQL Workbench by right clicking on the table, choose "Alter Table ..." in the menu. When the table structure view opens, go to tab "Options" (on the lower bottom of the view), and set "Auto Increment" field to the value of the next autoincrement number.
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),
...
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
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
