How to reset sequence if the table is empty? [duplicate]

Question

I am using Oracle SQL. There is a sequence and trigger used for id that increments from 1 every time a new record is added in the table. However if all the records are deleted and new records to be added this table, the id doesn't starts from 1 whilst it starts from the last maximum number of records recorded in the sequence.

Therefore is there a way in TRIGGER statement where I can reset the sequence if the table is empty and new records get imported.

OR do I have to do stored_procedure way, if that then how can i call this using myBatis mapper?

Table (customer_info)

customer_id customer_name customer_location
1           Arbin         USA
2           Tim           Canada
3           Rachel        Australia

Sequence

CREATE sequence customer_id_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Trigger

CREATE OR REPLACE TRIGGER customer_id 
BEFORE INSERT ON customer_info 
FOR EACH ROW

BEGIN
 SELECT customer_id_seq.NEXTVAL
 INTO   :new.customer_id
 FROM   dual;
END;
/

Thank you.

what's wrong with not using an auto-increment field in this case the customer_id_seq? reset back should be just TRUNCATE table <tablename> — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jun 12, 2017 at 20:59
@maSTAShuFu It's Oracle not the SQL Server and not an identity column. — Nick Krasnov
– Nick Krasnov, Commented Jun 12, 2017 at 21:01
Marked as duplicate. Note: There is no need to use dynamic sequence for that, unless you want to automate the process. — Nick Krasnov
– Nick Krasnov, Commented Jun 12, 2017 at 21:11

pmi · Accepted Answer · 2017-06-12 21:02:59Z

2

Once You Truncate the Table. The Sequence has to be Reset to 1.

The simplest way to do it it by dropping and re-creating it again.

 DROP SEQUENCE customer_id_seq
 CREATE sequence customer_id_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

answered Jun 12, 2017 at 21:02

pmi

3513 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nick Krasnov Over a year ago

Simplest? Probably. Best option? No. A better option would be to alter the sequence, hange increment by value to - <<current-value>> , generate next value, and change increment by value back to 1. If there is granted select privilege on that sequence to another user they should be re-granted again once you drop and recreate the sequence.

John Smith Over a year ago

@Mikolas Pansky, If I implement that way then it won't be robust, it would consume time everytime to DROP and CREATE. Therefore I was looking to make it automatic.

pmi Over a year ago

Of course the INCREMENT using negative value is very much the solution that's more elegant. And even without using the DDL.

John Smith Over a year ago

@NicholasKrasnov, you are right. I have to go through all those process you have mentioned.

Collectives™ on Stack Overflow

How to reset sequence if the table is empty? [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related