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?

Possible duplicate of [Creating a sequence for a varchar2 field in Oracle](http://stackoverflow.com/questions/14561210/creating-a-sequence-for-a-varchar2-field-in-oracle) — I_am_Batman, Apr 17 '16 at 10:33
You can't have a number (int, in your case) that is always seven digits if you start from 1; the leading zeros are not part of the number. You can have a seven-character column that you populate with a zero-padded sequence value; or you can have a number that you zero-pad for display (or in a virtual column). Which do you want? — Alex Poole, Apr 17 '16 at 11:12

I_am_Batman · Answer 1 · 2016-04-17T10:58:09.777

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

edited Apr 17 '16 at 10:58

answered Apr 17 '16 at 10:03

I_am_Batman

895
9
21

Thanks but is it possible to start with one and still have 7 digits? like 0000001 – ghjhgj jhgjhg Apr 17 '16 at 10:24
But I want to store it in the database in 7 digit form, not in query. How do I fit that into insert? – ghjhgj jhgjhg Apr 17 '16 at 10:42
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','Gotham'); – I_am_Batman Apr 17 '16 at 10:45
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. – I_am_Batman Apr 17 '16 at 10:46
not working I am getting a random numbers starting from 420. But I specified start with 1 – ghjhgj jhgjhg Apr 17 '16 at 10:51
Did you specify Increment by 1 nocache nocycle ? – I_am_Batman Apr 17 '16 at 10:53
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. – ghjhgj jhgjhg Apr 17 '16 at 11:35
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. – I_am_Batman Apr 17 '16 at 11:37
I have staff_id as INT, does it need to be varchar? – ghjhgj jhgjhg Apr 17 '16 at 11:37
Did we find any other solution? – I_am_Batman Apr 20 '16 at 17:56

score 0 · Answer 2 · edited Nov 18 '20 at 14:59

CREATE TABLE staff 
    ( 
        id         NUMBER(7) NOT NULL, 
            surname    VARCHAR2(50) NOT NULL, 
                firstnames VARCHAR2(50) NOT NULL, 
                    phone      VARCHAR2(30) NOT NULL, 
                        address    VARCHAR2(150) NOT NULL 
  );



ALTER TABLE Staff ADD (
  CONSTRAINT staff_pk PRIMARY KEY (ID));

    CREATE SEQUENCE staff_seq START WITH 1;

Trigger definition:

            CREATE OR REPLACE TRIGGER staff_bir 
                BEFORE INSERT ON Staff 
                    FOR EACH ROW

                        BEGIN
                            SELECT staff_seq.NEXTVAL
                                INTO   :new.id
                                    FROM   dual;
                                       END;

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. — Milo LaMar, Nov 18 '20 at 17:51

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

2 Answers2