CODEWITHSUNDEEP
sqloracle-databaseinsert
Luis Matos
Luis Matos

Reputation: 365

In Oracle, I want to use a sequence and not allow Insert on the column that uses the sequence

I want to make happen the same that happens when I do the following

CREATE TABLE "TEST1" 
(   
     "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY,
    "APPCODE" VARCHAR2(1)
);
  Table TEST1 created.
INSERT INTO TEST1 (ID, APPCODE) VALUES (1,'A');
   Error starting at line : 6 in command -
   INSERT INTO TEST1 (ID, APPCODE) VALUES (1,'A')
   Error at Command Line : 50 Column : 1
   Error report -
   SQL Error: ORA-32795: cannot insert into a generated always identity column
INSERT INTO TEST (APPCODE) VALUES ('A');
   1 row inserted.

but I want to use named sequences, created by me. I want the same behavior as when using the "ALWAYS" keyword (as in "GENERATED ALWAYS AS IDENTITY") and at the same time use my own named sequences, but I don't know how.

With named sequences, it seems to be impossible to avoid that an INSERT uses the ID COLUMN on the insert. But maybe there is a way? This is the question I'm asking. Below I create a named sequence and show the difference (I can't figure out how to prevent the ID column to be allowed on the insert).

CREATE SEQUENCE SEQ_TEST2 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE;
    Sequence SEQ_TEST2 created.
INSERT INTO TEST2 (APPCODE) VALUES ('A'); /* This is ok */
   1 row inserted.
INSERT INTO TEST2 (ID,APPCODE) VALUES (1928,'A'); /* This is NOT ok */
   1 row inserted.

The second insert above is what I want to prevent from happening, it shouldn't be possible to insert on the ID column. I don't care how to prevent it to happen, doesn't have to be the same way that the "ALWAYS" keyword on the TEST1 table works, but I would like to prevent it from happening. Anyone knows please how to to it?

Upvotes: 2

Views: 1235

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6094

When you define a column as a identity column, Oracle automatically creates a sequence, you just don't get to choose the name. You can view the name of the sequence that was created and will be used to populate the identity in the DATA_DEFAULT column of the ALL_TAB_COLS table.

SELECT owner,
       table_name,
       column_name,
       data_default
  FROM all_tab_cols
 WHERE identity_column = 'YES';

You can query the next value .nextval (and after that the current) value .currval of the sequence, but you can't reset or change its value directly (alter sequence ...), instead you'd need to change the table column, see oracle how to change the next autogenerated value of the identity column.

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

Why do you thing that while using IDENTITY you do not use a SEQUENCE?

Check the documentation or the example below

CREATE TABLE "TEST1" 
(   
     "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY,
    "APPCODE" VARCHAR2(1)
);

For this table Oracle creates a sequence for you under the over:

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
insert into TEST1 (APPCODE) values ('x');
---    
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL')); 

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST1        |       |       |            |          |
|   2 |   SEQUENCE               | ISEQ$$_75209 |       |       |            |          |
-----------------------------------------------------------------------------------------

Or check the dictionary

select SEQUENCE_NAME from USER_TAB_IDENTITY_COLS
where table_name = 'TEST1';

SEQUENCE_NAME                                                                                                                   
---------------
ISEQ$$_75209

In identity_options you can define the sequence options.

By selection ALWAYS or BY DEAFULT [ON NULL] you can adjust what is posible / now allowed to use in insert (I'm not sure from you description what is your aim).

Upvotes: 1

Related Questions