0

I have a table with a primary key sequence using the newer GENERATED BY DEFAULT AS IDENTITY feature:

CREATE TABLE test (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    data VARCHAR
);

I then give the table a few values to begin with, overriding the sequence:

INSERT INTO test(id,data) VALUES (3,'something');
INSERT INTO test(id,data) VALUES (6,'something else');

Now if I add a few more values, letting the sequence do its thing:

INSERT INTO test(data) VALUES ('whatever');
INSERT INTO test(data) VALUES ('stuff');
INSERT INTO test(data) VALUES ('etc');

… I get an error:

ORA-00001: unique constraint (FIDDLE_CEYTNFUWNIDRFXSPTDWJ.SYS_C0054804) violated

OK, I understand the error: apparently the sequence starts at the beginning and begins to overlap with existing values.

How do I correctly set the sequence after the initial INSERT?

I have a fiddle at: https://dbfiddle.uk/MtPocwBq

Manngo
  • 14,066
  • 10
  • 88
  • 110
  • @a_horse_with_no_name There was a useful solution for PostgreSQL, but I now need one for Oracle. I’ve changed the tag accordingly. – Manngo Apr 12 '23 at 11:48
  • 1
    [how to reset Identity column in Oracle](https://stackoverflow.com/questions/39295422/how-to-reset-identity-column-in-oracle) – Ponder Stibbons Apr 12 '23 at 12:04
  • 1
    1. `generated always` at create time. 2. `alter table modify id generated ... ` as in linked question – astentx Apr 12 '23 at 12:29
  • Does this answer your question? [how to reset Identity column in Oracle](https://stackoverflow.com/questions/39295422/how-to-reset-identity-column-in-oracle) – The Impaler Apr 12 '23 at 12:56
  • @PonderStibbons The accepted answer method 2 appears to do the job. Not the same question, but the answer did the job. Thanks. – Manngo Apr 13 '23 at 07:25

2 Answers2

1

Short answer is:

ALTER TABLE test MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);

Description in Oracle documentation:

START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.

And your modified dbfiddle.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
0

Reset the generated ID sequence using:

alter table test modify (id generated as identity (start with 7));

See modified fiddle.

The Impaler
  • 45,731
  • 9
  • 39
  • 76