Auto increment field in table

Question

I'm using oracle 11g xpress. I have created a simple table. I want that field each time a user inputs data to be incremented by 1, starting from 10000. Can you assist?

My table is this:

CREATE TABLE PROG_TITLE 
(
PR_ID       INT         NOT NULL,
PR_TITLE    VARCHAR2(128)   NULL,
PR_INDEX    INT             NULL
);

I have added the primary key:

ALTER TABLE PROG_TITLE 
ADD (CONSTRAINT mykey PRIMARY KEY (PR_ID));

And the sequence:

CREATE SEQUENCE PR_ID START WITH 10001 INCREMENT BY 1;

Now I can insert values as below (and it works):

INSERT INTO PROG_TITLE 
(PR_ID, PR_TITLE, PR_INDEX)
VALUES
(PR_ID.NEXTVAL, 'TEST1', 0);

My question is how can I do it without using NEXTVAL? How could I edit my query as to insert values only for PR_TITLE and PR_INDEX?

score 1 · Accepted Answer · answered Feb 01 '19 at 13:13

You'll need a trigger:

SQL> create or replace trigger trg_bi_pt
  2    before insert on prog_title
  3    for each row
  4  begin
  5    :new.pr_id := pr_id.nextval;
  6  end;
  7  /

Trigger created.

SQL> insert into prog_title (pr_title, pr_index)
  2  values
  3  ('test1', 0);

1 row created.

SQL> select * from prog_title;

     PR_ID PR_TITLE               PR_INDEX
---------- -------------------- ----------
     10001 test1                         0

SQL>

That's exactly what I wanted! Thanks a lot! – Konstantinos Feb 01 '19 at 20:10 — Konstantinos, Feb 01 '19 at 20:10

Auto increment field in table

1 Answers1