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?

Answer 1

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');