how to alter primary key to set Default value DEFAULT SYS_GUID() in ORACLE

Question

I create table in oracle and I want add auto increment for my primary key

CREATE TABLE "TEST_1"."PERSON" 
   ("ID" NUMBER NOT NULL ENABLE, 
    "FNAME" VARCHAR2(20 BYTE), 
    "LNAME" VARCHAR2(20 BYTE), 
     CONSTRAINT "PERSON_PK" PRIMARY KEY ("ID"));

Using Oracle sql develope when I want alter ID to get Auto Increment for primary key I get error ORA-02262: ORA-932

I have two raw in table

ALTER TABLE PERSON  
MODIFY (ID DEFAULT SYS_GUID() );

SYS_GUID() does not return a number, so the data type doesn't match. Do you actually want a GUID, rather than a number from a sequence, assigned via a trigger? (Unless you're on 12c, which has auto-increment functionality now). — Alex Poole
I want get auto increment for id in table.(11g).so only I can use a sequence thanks — noor
In 11g you'd usually use a sequence and a trigger for an 'auto-incrementing' number column, particularly for a PK. I'm not sure though if you want a number or a GUID. They are not the same. — Alex Poole

matsuninja matsuninja · Accepted Answer · 2017-11-17T20:10:09

(Note: my answer and examples are using Oracle 11g)

Reason for the issue/error

The Oracle error ORA-02262 is thrown when there are inconsistent data types. In your case, when creating the table you specify that the ID column is of type NUMBER:

CREATE TABLE "TEST_1"."PERSON" 
("ID" NUMBER NOT NULL ENABLE, ...

The SYS_GUID() function in Oracle "generates and returns a globally unique identifier (RAW value) made up of 16 bytes). The documentation then states that the 16-byte RAW value can be represented by a 32-character hexadecimal representation, or VARCHAR2(32).

For this reason, when SYS_GUID() is used as the default value of a column in Oracle, the result is often stored as a 32-byte VARCHAR or VARCHAR2:

CREATE TABLE TEST_1.PERSON (
  ID VARCHAR2(32) NOT NULL ENABLE DEFAULT SYS_GUID(),
  ...
);

Solution(s)

If altering the data type of the column is a suitable solution, the code below will successfully alter the table to store SYS_GUID() values as identifiers:

ALTER TABLE TEST_1.PERSON MODIFY( 
  ID VARCHAR2(32) DEFAULT SYS_GUID() 
);

If you must have a numeric value as the ID in your table, there is an excellent answer here:

How to create id with AUTO_INCREMENT on Oracle?

how to alter primary key to set Default value DEFAULT SYS_GUID() in ORACLE

2 Answers

Reason for the issue/error

Solution(s)