Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table
I am aware how to set auto_increment but when i try to set auto increment as S1 i am unable to do. since i need the primary key values should be S1,S2,S3 as following records. can any one please suggest the data type for that,
desc test_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(10) | NO | PRI | NULL | |
| name | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE test_1 AUTO_INCREMENT='S1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S1'' at line 1
mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID text not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
Best Answer
Autoincrement values can only be numeric (integer and floats types if I remember correctly).
If you want to store S1, ...., S1000, just store an integer, and return the extra S on select or on client side: