how to add prefix in id column with autoincrement in sql?

Question

i have multiple user like admin and vendor etc. Now i want to add prefix in id field value while inserting data like if use is admin then value of id field is "A_1" or user is vendor then the value should be "V_1", while id field is autoincremented.Anyone has idea about this please comment.Thank you.

You can't. You can use a view or computed column to create another column with the prefix. — Gordon Linoff, Sep 25 '18 at 11:48
you can make your ID programmatically incrementing. Read something about [identity](https://learn.microsoft.com/it-it/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017) — Simo, Sep 25 '18 at 11:49
@simo thank you. if you have any example then please post here. — sandip bharadva, Sep 25 '18 at 11:52
@sandy that means that you have to delete and create from 0 your table — Simo, Sep 25 '18 at 11:53
You can setup [Triggers](https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html) on the table **After Insert** — Madhur Bhaiya, Sep 25 '18 at 11:53
Possible duplicate of [How to make MySQL table primary key auto increment with some prefix](https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) — Ankit Bajpai, Sep 25 '18 at 11:57
You can refer this - https://stackoverflow.com/questions/18405896/is-there-a-way-to-insert-an-auto-incremental-primary-id-with-a-prefix-in-mysql-d — Ankit Bajpai, Sep 25 '18 at 11:58
@Simo, that wasn't very rude. Rather a remark that such prefixes are a bit old-fashioned. — jarlh, Sep 25 '18 at 13:15

Simo · Answer 1 · 2018-09-25T12:35:33.763

0

For auto incrementing an ID you can read something about auto increment on sql:

Some example of Identity used to auto increment an ID used as a Primary Key:

CREATE TABLE Test_AutoIncrement(
    ID int AUTO_INCREMENT,
    Name varchar(50) NULL,
    Something varchar(100) NOT NULL,
    PRIMARY KEY(ID));

In this case you didn't have to specify the ID filed during any INSERT statement, SQL will manage that.

For example we have some records of this tabel like:

ID  |  Name  |  Something  |
1     "Simo"      "Foo"
2     "Fred"      "Bar"

Now, we simulate an Insert statement on our Test_AutoIncrement table:

INSERT INTO Test_AutoIncrement
VALUES(
        NULL,
        "FooBar()"
      );

Now our Table will be as follow:

ID  |  Name  |  Something  |
 1    "Simo"     "Foo"
 2    "Fred"     "Bar"   
 3     NULL      "FooBar()"

Remarks:

In the above case we start with ID=1 and it will increment it by 1 on every new record.

You can specify ID=0 in the insert if you want to start counting from zero but NO_AUTO_VALUE_ON_ZERO must be disabled from MySQL.

edited Sep 25 '18 at 12:35

answered Sep 25 '18 at 12:01

Simo

955
8
18

This looks like a sql server answer for a mysql question , mysql does not have identity, – P.Salmon Sep 25 '18 at 12:23
@P.salmon I studied mysql at school 2 years ago and we stuided identity, and we used identity. Still during the final exam (at the end of the 5 years) we used Identity, and the exam was on mysql. Are you sure? – Simo Sep 25 '18 at 12:27
And I'm pretty sure we was using mysql because it's open source, and i coded alot of "identity" during my period at school, in mysql – Simo Sep 25 '18 at 12:28
I am sure, mysql uses auto_increment see https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html. Also there is no such data type as nvarchar in mysql. But your solution does not meet the requirement anyway the question does not call for a simple incrementing id. – P.Salmon Sep 25 '18 at 12:32
@P.Salmon I replaced `nvarchar` with `varchar` – Simo Sep 25 '18 at 13:21

score 0 · Answer 2 · answered Sep 25 '18 at 12:08

you can do it with sequence and trigger here is a working example

create table tbl_usertypes(
 usertypes_id varchar2(50),
 name varchar2(50),
 usertype varchar2(50),
 CONSTRAINT pk_usertypes_id PRIMARY KEY(usertypes_id)
);

desc tbl_usertypes;

CREATE SEQUENCE sq_usertypes_id START WITH 1;

create or replace trigger add_random_id
BEFORE INSERT ON tbl_usertypes
FOR EACH ROW
BEGIN
  IF :NEW.usertype = 'admin' THEN
        SELECT 'A_' || sq_usertypes_id.NEXTVAL INTO :NEW.usertypes_id FROM dual;
    ELSE 
        SELECT 'B_' || sq_usertypes_id.NEXTVAL INTO :NEW.usertypes_id FROM dual;
    END IF;
END;

INSERT INTO tbl_usertypes values(null,'hoax', 'admin');
INSERT INTO tbl_usertypes values(null,'hoax', 'user');
select * from tbl_usertypes;

and the result

This looks like an oracle answer to a mysql question. Mysql does not have sequence — P.Salmon, Sep 25 '18 at 12:23

how to add prefix in id column with autoincrement in sql?

2 Answers2