How can I reset the sequence to max ID after inserts to the table with key?

[mert_karakilic]

Consider this scenario;

You have a table and a primary key within it created as follows as part of your create statement;

CREATE COLUMN TABLE "TABLE1" (

  "ID" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1)

  ,"NAME" NVARCHAR(255) NOT NULL

  ,PRIMARY KEY ("ID")

  );

Let's say during the initial data load, you have added rows giving the ID value in your insert statement due to dependencies on other tables and foreign keys using this table like below;

INSERT INTO TABLE1 (ID, NAME) VALUES (1, "Key 1");

INSERT INTO TABLE1 (ID, NAME) VALUES (2, "Key 2");

Then if you try to insert new rows in future without the ID value, you get an error saying "[301]: unique constraint violated:". You know the issue as the sequence number is not incremented when you added the rows providing the key.

So, your only option the way I see it is the either not give the ID as part of the input to let the system handle it or not use the IDENTITY at all and create your own sequence which you can manipulate later. Because these system generated sequences via IDENTITY feature is not possible to alter. When you try to reset (alter sequence "SYSTEM"."_SYS_SEQUENCE_1527547_#0_#" restart with 3), you get an error like below;

"SAP DBTech JDBC: [383]: invalid identifier: _sys_ prefix not allowed: "

That is of course after finding the name of your sequence via;

select '"' || SCHEMA_NAME || '"."' || SEQUENCE_NAME || '"' AS SEQUENCE_NAME from SEQUENCES

WHERE RESET_BY_QUERY LIKE '%TABLE1%';

So, first of all, is there a way to reset the sequence to the max ID in the table after the inserts? If not, what would be your recommendation to overcome this issue? How do you access the current or next value in the sequence in these auto generated sequences?

Thanks in advance.

[lbreddemann · Accepted solution]

Hi Mert

So, first of all, is there a way to reset the sequence to the max ID in the table after the inserts? 
If not, what would be your recommendation to overcome this issue? 
How do you access the current or next value in the sequence in these auto generated sequences?

Nope, there's no way to temper with the max ID of a system generated object (e.g. the identity column).

To overcome this, you will have to use sequences manually, instead of identity column.

And there is no officially support way to access the current value. Technically you can use the the <sequence_name>.nextval but this will increase the value.

I'd say the general guidance here is: if you need any control over the sequence, use sequences and skip the identity column.

[mert_karakilic]

That is what I was afraid of. Thanks Lars for confirming. That means like you said I will have to create my own sequences which is okay, just a little extra overhead. I hope they fix this issue somehow in future. Thanks again.

[lbreddemann]

Maybe it would be worthwhile for you to contact the SAP HANA product management for this.

[lbreddemann]

In addition to that, I've got to say this:

IDENTITY columns to me are really 'just' syntactic sugar.

What I like is of course that the table DDL clearly represents the behavior of the column and to a certain degree the intended use (it's still required to define the primary key, though, just as you did).

What I don't like is the fact that the column literally disappears in the code where records are created.

I'd argue that the table definition probably is read less often then the CREATE RECORD code and it would be good to know where those ID values come from.

So, while I think IDENTITY can be convenient shortcut, I don't believe that it makes all that much more work to simply use a custom sequence.

In my eyes the best option here would be to extend the current IDENTITY column model with the option to use a custom sequence. That way people who like to not have to put in <seq>.nextval into their CREATE RECORD code can still use the automatism but also get the option to administer the number dispenser aka sequence as they like.

[sathish_kumar82]

This message was moderated.