how to add prefix to identity column in mysql.?

Question

Here i am trying to auto increase my identity column in mysql databse like the following with a prefix adding before the number.

Id------------------Category

Cat10001---------Men
Cat10002--------Women
Cat10003---------Kids

here is the link which i referred to do so but i got hung up with an error

Error: #1064 - 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 'AS 'ABCD-' + CAST(Id as varchar(16)) )' at line 4

MySql Query:

CREATE TABLE MyTable
(
    Id int NOT NULL PRIMARY KEY,
    categoryid AS 'cat' + CAST(Id as varchar(16)) 
)

Note: i am using PhpMyadmin mysql.

MySQL doesn't support calculated columns. Create a view instead. — gvee, Mar 11 '14 at 17:45
what is a view can you please give a small explanation so that i can carry on with that..!! — 3bu1, Mar 11 '14 at 17:46
[Here](http://dev.mysql.com/doc/refman/5.0/en/create-view.html) — Zane, Mar 11 '14 at 17:49

score 3 · Accepted Answer · answered Mar 11 '14 at 18:55

3

You can use BEFORE INSERT trigger. You can do something like this. Check below to see if this is what you needed.

CREATE TABLE MyTable
(
    Id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    categoryid varchar(16),
    categoryname varchar(16)
);

CREATE TRIGGER mytrigger
  BEFORE INSERT
  ON MyTable
  FOR EACH ROW
  SET NEW.categoryid = 
  CONCAT("CAT",COALESCE((SELECT MAX(Id)+1 from MyTable),1));

insert into MyTable(categoryid,categoryname) Values(1,"test");
insert into MyTable(categoryid,categoryname) Values(2,"test1");

answered Mar 11 '14 at 18:55

Charls

319
1
9

what ever you did is right but i want categoryid as primarykey but here ID is primary key when i try to make categoryid as primarykey and ID remain same as AUTO_INCREMENT,it is throwing an error can you please help me how to make category id as primary key? – 3bu1 Mar 12 '14 at 17:29
You can only define a column as AUTO_INCREMENT if it is a PRIMARY KEY. you can make the categoryid as unique, if it best fits your needs. Look here for a brief explanation - http://stackoverflow.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql/3844913#3844913 – Charls Mar 12 '14 at 17:50
primarykey in this table will become foreign key in other table.can i make a unique key coloumn as foreign key in other table? – 3bu1 Mar 12 '14 at 17:58

Rahul · Answer 2 · 2014-03-11T17:55:25.760

0

Create a view or a separate table to have this type of data. Like

create view calculateddata
as
select Id,
concat('cat',CAST(Id as varchar(16))) AS categoryid
from MyTable

edited Mar 11 '14 at 17:55

answered Mar 11 '14 at 17:47

Rahul

76,197
13
71
125

score 0 · Answer 3 · answered Mar 11 '14 at 17:48

MySQL doesn't support calculated columns so a view will be required instead.

You should really read the answer here: Create a computed column based on another column in MySQL

CREATE VIEW your_view
  AS
SELECT Concat('CAT', Cast(Id As varchar(11))) As Id
     , categoryid
FROM   MyTable

how to add prefix to identity column in mysql.?

3 Answers3