• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

Autonumber PK ID - is it Possible in Oracle

Hello,
   I just wanted to know how can I have a Primary Key be an Auto number. In Access you can specify it to be a auto number and so it just increases with each record added. How can I do that in oracle. For Example

CREATE TABLE TEST
(
 tableID NUMBER(6) not null,
 column2 VARCHAR(3),
 column3 DATE,
 CONSTRAINT tableID_pk PRIMARY KEY (tableID));

so How can I have tableID increase it self with each record added.
Thanks in advance
Adil

 
0
AdilK
Asked:
AdilK
  • 2
1 Solution
 
BobMcCommented:
You can implement this functionality with a trigger and a sequence:

CREATE SEQUENCE TEST_SEQ START WITH <num of rows in table+1> NOCACHE;

CREATE TRIGGER FOR INSERT on TEST
CREATE TRIGGER TEST_TRIG
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
      SELECT TEST_SEQ.NEXTVAL INTO :NEW.tableID
      FROM DUAL;    
END;    
/

Now each time you insert a row, the trigger will populate your column with the next number from the sequence.

HTH
Bob
0
 
BobMcCommented:
Oops - trugger should just read

CREATE TRIGGER TEST_TRIG
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
     SELECT TEST_SEQ.NEXTVAL INTO :NEW.tableID
     FROM DUAL;    
END;    
/

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now