0

I have table with two rows which one ID with auto increment and there are much row last number ID is 89. And then I truncate data/row in the table. And then I insert row again.

But number ID from 90 not from 1 (one). If in mysql if I truncate data in table auto increment start from 1 (one) again. So how in oracle I want to ID autoincrement from one again. Thanx.

Below step when I create table:

// create table;
CREATE TABLE tes (
  id NUMBER NULL,
  ip_address varchar2(25) NOT NULL
  PRIMARY KEY (id)
);

// and create increment;
CREATE SEQUENCE tes_sequence START WITH 1 INCREMENT BY 1;

// and create trigger;
CREATE OR REPLACE TRIGGER tes_trigger
BEFORE INSERT
ON tes
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT tes_sequence.nextval INTO :NEW.ID FROM dual;
END;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Loren Ramly
  • 1,091
  • 4
  • 13
  • 21

1 Answers1

5

Oracle sequence is a separate object and is not connected with table. If you need to start sequence after truncating a table you need to alter the sequence. Have a look here: How do I reset a sequence in Oracle?

Community
  • 1
  • 1
MiGro
  • 1,471
  • 10
  • 8