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

alteroraclesql

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

Best Answer

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

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.