How do you create an auto increment in Oracle 11g?

Question

I want to generate a 7 DIGIT identifier for primary key in Oracle 11g. The primary key must be 7 digits long.

Here is my code:

CREATE SEQUENCE sequence_staff
MINVALUE 1
START WITH 1
INCREMENT BY 1;

CREATE TABLE Staff (
    Staff_ID    INT PRIMARY KEY, --Autoincrement 7 digit --PK
    Surname     VARCHAR2(50) NOT NULL,
    Firstnames  VARCHAR2(50) NOT NULL,
    Phone       VARCHAR2(30) NOT NULL,  --D1
    Address     VARCHAR2(150) NOT NULL
);

Currently the primary key is 1 (1 digit), not 0000001 (7 digits). Any ideas?

Solution

You have to modify the sequence as follows :

CREATE SEQUENCE sequence_staff
MINVALUE 1000000
START WITH 1000000
INCREMENT BY 1 NOCACHE NOCYCLE;

Also, you have to insert the new staff_id column using sequence_staff.nextval always. See how it works hitting

select sequence_staff.nextval from dual; --repeated times.

Read more about sequences here https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm

EDIT :

Yes, it is possible. Create sequence the way you were creating and :

select to_char(sequence_staff.nextval,'FM0000000') from dual;

EDIT 2 :

This link deserves the credit. http://stackoverflow.com/questions/14561210/creating-a-sequence-for-a-varchar2-field-in-oracle

EDIT 3 : If you really want the results your way in Oracle Database you have to :

1. alter table staff modify staff_id varchar(20);
2. CREATE SEQUENCE sequence_staff
MINVALUE 1
START WITH 1
INCREMENT BY 1 NOCACHE NOCYCLE;
3. insert into staff(Staff_id, surname,firstnames, phone,address) values(to_char(sequence_staff.nextval,'FM0000000'),'Wayne','Bruce','0000','Gotha‌​m'); 

Comments

[ghjhgj jhgjhg]

Thanks but is it possible to start with one and still have 7 digits? like 0000001

[ghjhgj jhgjhg]

But I want to store it in the database in 7 digit form, not in query. How do I fit that into insert?

[I_am_Batman]

Whenever you insert into the database, you should use : insert into staff(Staff_id, surname,firstnames, phone,address) values(to_char(sequence_staff.nextval,'FM0000000'),'Wayne','‌​Bruce','0000','Gotha‌​m');

[I_am_Batman]

If you are looking for something like autoincrement in MySQL, that doesn not exist in Oracle. You have to use sequences. Also, you will have to change datatype of primary key as varchar. Cannot use int, for your requirement.

[ghjhgj jhgjhg]

not working I am getting a random numbers starting from 420. But I specified start with 1

[I_am_Batman]

Did you specify Increment by 1 nocache nocycle ?

[ghjhgj jhgjhg]

Thanks batman. I had it before and wasnt working so removed it. But server is down now so i can't run the code on my server.

[I_am_Batman]

Try at leisure. There is no reason it shouldn't work, if you are cool treating varchar as primary key datatype. If you keep it int, same code will work , but will cast 000001 as 1 and so on. In other words, your requirement cant be met.

[ghjhgj jhgjhg]

I have staff_id as INT, does it need to be varchar?

[I_am_Batman]

Did we find any other solution?

[Milo LaMar]

As stated in the comments, he wants the number to always be stored as 7 digits, so this doesn't work for his needs. He'll need to use a varchar or start from 1000000.