create table with sequence nextval in oracle

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

User · Answer

I for myself prefer Lukas Edger s solution     But you might want to know there is also a function SYS GUID which can be applied as a default value to a column and generate unique ids   you can read more about pros and cons here

User · Answer

In Oracle 12c you can also declare an identity column  CREATE TABLE identity test tab     id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY    description VARCHAR2 30       examples  amp  performance tests here     where  is shorts  the conclusion is that the direct use of the sequence or the new identity column are much faster than the triggers

User · Answer

You can use Oracle s SQL Developer tool to do that  My Oracle DB version is 11   While creating a table choose  Advanced option and click on the Identity Column tab at the bottom and from there choose  Column Sequence  This will generate a AUTO INCREMENT column  Corresponding Trigger and Squence  for you

User · Answer

In Oracle 12c  you can now specify the CURRVAL and NEXTVAL sequence pseudocolumns as default values for a column  Alternatively  you can use Identity columns  see    reference doc articles  Enhancements in Oracle DB 12cR1  12 1   Default Values for Table Columns and Identity Columns in 12 1   E g    CREATE SEQUENCE t1 seq  CREATE TABLE t1     id          NUMBER DEFAULT t1 seq NEXTVAL    description VARCHAR2 30

User · Answer

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

[sql] create table with sequence.nextval in oracle

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to plsql