CODEWITHSUNDEEP
javasqloracle-database
kittu
kittu

Reputation: 7018

How to auto adjust ID value (primary key) in oracle?

Right now I have created a sequence and a trigger to auto increment the ID value like this:

CREATE OR REPLACE TRIGGER "RTH"."TBL_USER_TRIGGER" 
BEFORE INSERT ON TBL_USER
FOR EACH ROW
BEGIN
SELECT TBL_USER_SEQ.nextval
INTO :new.USR_ID
FROM dual;
END;

ALTER TRIGGER "RTH"."TBL_USER_TRIGGER" ENABLE;

Let say I have three rows:

User ID      FIRSTNAME     LASTNAME
====================================

1              John        smith
2              James       smith
3              Pat         smith

When I delete the first row(1) I want the ID values to auto correct itself to correct numbers so that second row ID values becomes 1 and third row ID values becomes 2

Is it possible in oracle? or do I have do it through code as I am using Java to insert records into table when user submits a form.

Java DAO class:

public class RegistrationDAO {

    public void insert(User user) {

        try {            
            Connection con = DBConnection.getConnection();
            String query = "insert into TBL_USER(USR_FIRST_NAME,USR_LST_NAME,USR_PRIMARY_EMAIL,USR_PASSWORD) values(?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(query);

            pst.setString(1, user.getFirstName());
            pst.setString(2, user.getLastName());
            pst.setString(3, user.getEmail());
            pst.setString(4, user.getPassword());
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("@@@@Record insertion error in Registration DAO@@@@");
            System.out.println(e);
        }
    }
}

Upvotes: 1

Views: 235

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The simple answer is: "No, you don't want to do that." The purpose of an id is to uniquely identify each row. A sequential id also has the feature that it provides insertion order. It is not intended to change over time. Row 1 is Row 1 is Row 1.

If you want ordering, then declare the id to be the primary key and use a query such as this:

select t.*, row_number() over (order by usr_id) as seqnum
from t;

Upvotes: 3

Vidya Pandey
Vidya Pandey

Reputation: 211

Ideally, you should not be changing USER ID values. But still if there is requirement. Then below are the steps.

  1. Create a procedure to reset the sequence TBL_USER_SEQ. This is needed as you are resetting the USER ID values. So after resetting, whenever you insert new record, Sequence should start with proper value ( i.e. MAX USER_ID value of the table). So that there will be no gap in USER_ID
  2. Write Delete Trigger on table which will adjust USER_ID values.

Procedure to reset sequence

CREATE OR REPLACE PROCEDURE reset_sequence_p (p_seq IN VARCHAR2, p_new_seq NUMBER)
AS
    l_value NUMBER;
BEGIN

    -- Select the next value of the sequence
    EXECUTE IMMEDIATE 'SELECT ' || p_seq || '.NEXTVAL FROM DUAL' INTO l_value;

    -- Alter the sequnce to increment by the difference
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq || ' INCREMENT BY ' || ( p_new_seq - l_value ) ; 

    -- Increment to next value after diference
    EXECUTE IMMEDIATE 'SELECT ' || p_seq || '.NEXTVAL FROM DUAL' INTO l_value;

    -- Set the increment back to 1
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq || ' INCREMENT BY ' || 1 ; 

END reset_sequence_p;

Delete Trigger

CREATE OR REPLACE TRIGGER "RTH"."TBL_USER_TRIG_DEL"
    BEFORE DELETE
    ON TBL_USER
    FOR EACH ROW
DECLARE
    v_new_user_id_seq   NUMBER;
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- Update USER_ID value for all remaining records greater than USER_ID that got deleted.
    UPDATE TBL_USER
    SET USER_ID = USER_ID - 1
    WHERE USER_ID > :old.USER_ID;

    -- Retrieve max USER_ID available in table. 
    SELECT MAX (user_id) INTO v_new_user_id_seq FROM TBL_USER;

    -- Call procedure to reset sequence
    reset_sequence_p ('TBL_USER_SEQ', v_new_user_id_seq);

    COMMIT;
END;

NOTE : Make sure that Primary key on table is disabled before execution of Delete statement. I hope this helps.

Upvotes: 0

Related Questions