CODEWITHSUNDEEP
sqloracle-database
mpluse
mpluse

Reputation: 1875

Invalid numbers on sequence

When I created a sequence for a table article, it's started from 17 not 1

CREATE SEQUENCE seq_article START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER auto_article BEFORE insert ON article
FOR EACH ROW
BEGIN
  SELECT seq_article.NEXTVAL INTO :NEW.id_article FROM dual;
END;
/

I tried to delete all rows and creat other data, this time it's started from 19. How can I fix that?

Upvotes: 0

Views: 1066

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

I'm not sure that I understand the problem.

  • A sequence generates unique values. Unless you set the sequence to CYCLE and you exceed the MAXVALUE (not realistically possible given the definition you posted) or you manually reset the sequence (say, by setting the INCREMENT BY to -16, fetching a nextval, and then setting the INCREMENT BY back to 1), it won't ever generate a value of 1 a second time. Deleting the data has no impact on the next id_article that will be generated.
  • A sequence-generated column will have gaps. Whether because the sequence cache gets aged out of the shared pool or because a transaction was rolled back, not every value will end up in the table. If you really need gap-free values, you cannot use a sequence. Of course, that means that you would have to serialize INSERT operations which will massively decrease the scalability of your application.

Upvotes: 1

Related Questions