AdilK
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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