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

Question

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?

Before 12c, [the standard way was to use a sequence + a trigger](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle/11296469#11296469). There's a lot of other examples on here if you search. — kfinity, Jan 14 '22 at 15:33
*"but I want to use sequences"* - why do you want to use sequences? — William Robertson, Jan 14 '22 at 19:28
@WilliamRobertson sequences appear as separate objects, can be created and dropped, and don't give differences between databases on sqldeveloper. At the moment I have dozens of bogus differences between tables that are exactly the same on sqldeveloper, because the ID columns use internal system sequences which are differently named. It is a problem on sqldeveloper diff yes, but I wanted to solve it with this workaround. Database diff is very important to my work — Luis Matos, Jan 21 '22 at 13:58
_" but I wanted to solve it with this workaround. Database diff is very important to my work"_ It would appear that your preferred "work around" is simply not available. So then what? How about changing your work around to something - even a manual procedure - to deal with the fact that a 'diff' will report differences that really have no consequence. — EdStevens, Jan 21 '22 at 14:24
@LuisMatos thanks for the explanation. So the issue is with the generated sequence name. Unfortunately there is no supported way to specify a custom name (you could probably manually update the dictionary entry, but that could have unexpected consequences so better not). You can specify any other attribute of the generated sequence (start with, increment by, cache etc). Perhaps this is a SQL Developer question though - maybe it has some option to ignore the name in schema diff reports? (I don't use it much so wouldn't know.) — William Robertson, Jan 21 '22 at 15:04
@EdStevens sure I can write programs to read and eliminate differences that have no consequence, I just wanted to know if there was a way to avoid inserting an ID column when using a named sequence. If there isn't, then I'll have to deal with it ofc. — Luis Matos, Jan 21 '22 at 15:56
@WilliamRobertson unfortunately sqldeveloper has many issues, the diff tool has many small and annoying bugs and it makes it really hard for us who work with diffs between databases all the time. It does ignore the name on indexes for example but not on sequences - even if the sequences are defined exactly the same, are used in the same table and in the same column it still reports the column as a different one. Guess I'll just have to live with it — Luis Matos, Jan 21 '22 at 16:00

Marmite Bomber · Answer 1 · 2022-01-14T16:04:34.193

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

Hi Jan. I know it creates sequences, but somehow the "always" keyword makes it impossible to insert using the "ID" column. I want to be able to do the same but using my own named sequence. — Luis Matos, Jan 19 '22 at 18:57

score 1 · Answer 2 · answered Jan 14 '22 at 16:01

1

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

answered Jan 14 '22 at 16:01

EJ Egyed

5,791
1
8
23

Hi EJ. My wish is to emulate the behavior of the ALWAYS keyword when I create a sequence by hand. I didn't find how to do it. It seems that it is only available when we create the identity using the keyword ALWAYS. – Luis Matos Jan 20 '22 at 11:55

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

2 Answers2