4

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() );
noor
  • 41
  • 1
  • 4
  • 2
    [SYS_GUID()](http://docs.oracle.com/cloud/latest/db121/SQLRF/functions201.htm#SQLRF06120) 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](http://docs.oracle.com/cloud/latest/db121/SQLRF/statements_6017.htm), assigned via a trigger? (Unless you're on 12c, which has [auto-increment functionality](https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF55657) now). – Alex Poole Jun 24 '15 at 11:25
  • I want get auto increment for id in table.(11g).so only I can use a sequence thanks – noor Jun 24 '15 at 11:28
  • 1
    In 11g you'd usually [use a sequence and a trigger](http://stackoverflow.com/q/9733085/266304) 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 Jun 24 '15 at 11:32

2 Answers2

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?

matsuninja
  • 275
  • 3
  • 12
-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 ??

ROHIT SHUKLA
  • 11