We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post.
CREATE TABLE qname
qname_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
qname VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.
The standard way to have "auto increment" columns in Oracle is to use triggers, e.g.
CREATE OR REPLACE TRIGGER my_trigger
FOR EACH ROW
-- Optionally restrict this trigger to fire only when really needed
WHEN (new.qname_id is null)
-- Select a new value from the sequence into a local variable. As David
-- commented, this step is optional. You can directly select into :new.qname_id
SELECT qname_id_seq.nextval INTO v_id FROM DUAL;
-- :new references the record that you are about to insert into qname. Hence,
-- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
-- obtained from your sequence, before inserting
:new.qname_id := v_id;
Read more about Oracle TRIGGERs in the documentation
Thank you very much! I am a newbie. May i know why you have used the line, :new.id := v_id; ? Pls help!
I'll update the answer. Note, I had a typo. It should be :new.qname_id, not :new.id
Now, I am pretty clear about this. Thanks man!
+1 for the trigger method, but i think you could lose the v_id variable completely and just select into the :new.qname_id. You might also just fire the trigger when new.qname_id is null as it allows code to reference the sequence nextval directly in the insert and bypass trigger execution.
@LukasEder, I don't think that the null check is a good idea- lets say that nextval=10 and someone inserted a record with qname_id=10000, everything will be fine! untill one day (perhaps a year later) nextval will reach 10000 ...