create table with sequence.nextval in oracle [duplicate]

Question

i created a sequence using the following query,

create sequence qname_id_seq start with 1 increment by 1 nocache; 

Now when i try to create a table which uses the above sequence, it is throwing the following error,

Error report: SQL Error: ORA-00907: missing right parenthesis 00907. 00000 -  "missing right parenthesis" 

I used the following query to create a table with sequence.nextval,

CREATE TABLE qname (     qname_id integer NOT NULL default qname_id_seq.nextval PRIMARY KEY,     qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE ); 

Answer 1

Oracle 12c

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.

So, your table creation would look like this:

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

Oracle 11g and below

According to the documentation, you cannot do that:

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   BEFORE INSERT    ON qname   FOR EACH ROW   -- Optionally restrict this trigger to fire only when really needed   WHEN (new.qname_id is null) DECLARE   v_id qname.qname_id%TYPE; BEGIN   -- 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; END my_trigger; 

Read more about Oracle TRIGGERs in the documentation