CODEWITHSUNDEEP
oracle-database
Loren Ramly
Loren Ramly

Reputation: 1111

Truncate data and if insert new row column increment from 1

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;

Upvotes: 0

Views: 1955

Answers (1)

MiGro
MiGro

Reputation: 1511

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?

Upvotes: 5

Related Questions