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

Answer 1

The oracle show this error means: // *Cause: New column datatype causes type-checking error for existing column // default value expression. // *Action: Remove the default value expression or don't alter the column // datatype.

are you not using Sequences for it ??

Answer 2

(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?