Search code examples
databaseplsqlprimary-keydefault

How to use default primary key as a sequence value when creating table for oracle database columns

We have a tool and this tool create some insert scripts to add rows in our plsql table. this scripts is not modifiable and we can't see this scripts. so,

when data row comes (with insert script that we don't know structure), we should give a primary key. we can use trigger but we don't want do this for reasons of performance.

Below code doesn't work.

CREATE TABLE qname
  (
    qname_id integer NOT NULL default qname_id_seq.nextval PRIMARY KEY,
   );

Any idea? Thanks..

Solution

  • .nextval cannot be used in Default Value of table, This is achieved by Trigger and Sequence when you want serialized number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like qname_id) by this way, and value of this column is not much considerable, you can use SYS_GUID() at Table Creation to get Auto Increment like this.

    CREATE TABLE qname 
(qname_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
name VARCHAR2(30));

    (or by Modifying Column)

    Now your qname_id column will accept "globally unique identifier value". you can insert value in table by ignoring emp_id column like this.

    INSERT INTO qname (name) VALUES ('name value');

    So, it will insert unique value to your qname_id Column.