CODEWITHSUNDEEP
sqldatabaseoracle-database
Dan Smith
Dan Smith

Reputation: 290

Auto increment an ID with a string prefix in oracle sql?

I have created the following table and I wish to have the userID auto-increment whenever there is a row added to the database. However I would like the ID to be formatted as 000001 for example as below, since there are a few tables and it would be ideal to give each ID a string prefix:

userID
----------
user000001
user000002
user000003


CREATE TABLE UserTable (
userID VARCHAR(20),
username VARCHAR(250) NOT NULL,
firstName VARCHAR(250) NOT NULL,
lastName VARCHAR(250) NOT NULL,
CONSTRAINT pkUserID
    PRIMARY KEY (userID),
CONSTRAINT uniUsername
    UNIQUE (username)
);

Upvotes: 1

Views: 7840

Answers (2)

Michael-O
Michael-O

Reputation: 18405

The prefix user is absolute pointless because it is attached to every ID. Drop it and use an ID NUMBER only. Plus, follow Jugal's advice.

Upvotes: 1

Jugal Shah
Jugal Shah

Reputation: 3701

You would have to use a combination of trigger and sequence as shown in the code below:

CREATE SEQUENCE CREATE SEQUENCE usertable_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
/

CREATE OR REPLACE TRIGGER usertable_trigger  
BEFORE INSERT ON UserTable
FOR EACH ROW

BEGIN
  SELECT 'user' || to_char(usertable_seq.NEXTVAL, '000099')
  INTO   :new.userID
  FROM   dual;
END;
/

Upvotes: 4

Related Questions