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

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 sequences. What happens is that using the "ALWAYS" keyword, it is not allowed to insert anything on the "ID" column. With sequences, it is possible to do almost the same, but not prevent that value to be provided on the insert, unfortunately, or I don't know how to do it.

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?

CodePudding user response：

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).

CodePudding user response：

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';