How to Reset Primary Key Sequence in PL/SQL

Resetting a PL/SQL primary key sequence is an important task to ensure that the sequence values align with the current state of the table. When a sequence falls out of sync with the data in a table, it can lead to issues where new records receive primary key values that conflict with existing data.In this article, we will see How to reset the PL/SQL primary key sequence when it falls out of sync. using the syntax, and methods and with some of the examples that will help to understand the process.

How to Reset the Sequence in Oracle Plsql?

In PL/SQL, sequences are commonly used to generate unique primary key values for tables. However, manual inserts, deletes or other operations can cause the sequence to become out of sync with the actual data. Resetting the sequence involves finding the maximum current value in the table and adjusting the sequence to continue from that point.

Syntax:

The syntax for resetting a PL/SQL sequence involves identifying the maximum current value in the table and adjusting the sequence using the ALTER SEQUENCE statement:

-- Syntax for resetting a PL/SQL sequence
ALTER SEQUENCE sequence_name INCREMENT BY value;
SELECT sequence_name.NEXTVAL FROM dual;
ALTER SEQUENCE sequence_name INCREMENT BY 1;

Examples of reset PL/SQL primary key sequence when it falls out of sync?

Example 1: Using ALTER SEQUENCE Statement

Consider a scenario where the employees table has gaps in sequence values. Using the ALTER SEQUENCE statement, we can dynamically adjust the sequence seq_employee_id based on the maximum current employee_id in the table.

-- Schema
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50)
);

CREATE SEQUENCE seq_employee_id START WITH 1 INCREMENT BY 1;

-- Sample Data
INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe');
INSERT INTO employees (employee_id, employee_name) VALUES (3, 'Jane Smith');
INSERT INTO employees (employee_id, employee_name) VALUES (5, 'Bob Johnson');

-- Resetting the sequence using ALTER SEQUENCE
DECLARE
    max_id INT;
BEGIN
    SELECT MAX(employee_id) INTO max_id FROM employees;
    
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_employee_id INCREMENT BY ' || (max_id + 1);
    SELECT seq_employee_id.NEXTVAL FROM dual;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_employee_id INCREMENT BY 1';
END;

Output:

-- No explicit output; the sequence is reset for future use.

Explanation:

• The sequence seq_employee_id is reset by adjusting the increment value based on the maximum current employee_id in the table.
  
• The sequence is then used to generate a new value, and the increment is set back to 1.

Example 2: Resetting a Sequence for a Table with Continuous Values

Assume a table named products with a primary key column named product_id and a corresponding sequence named seq_product_id.

-- Schema
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

CREATE SEQUENCE seq_product_id START WITH 1 INCREMENT BY 1;

-- Sample Data
INSERT INTO products (product_id, product_name) VALUES (1, 'Laptop');
INSERT INTO products (product_id, product_name) VALUES (2, 'Smartphone');
INSERT INTO products (product_id, product_name) VALUES (3, 'Tablet');

-- Manually resetting the sequence
DECLARE
    max_id INT;
BEGIN
    -- Manually determine the desired value (e.g., 100)
    max_id := 100;
    
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_product_id INCREMENT BY ' || (max_id + 1);
    SELECT seq_product_id.NEXTVAL FROM dual;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_product_id INCREMENT BY 1';
END;

Output:

-- No explicit output; the sequence is reset for future use.

Explanation:

• The sequence seq_product_id is reset based on the maximum current product_id in the table.
  
• The sequence is then used to generate a new value, and the increment is set back to 1.

Example 3: Automatically Detecting and Adjusting the Sequence

Example: Automatic Reset of Sequence for Orders Table

-- Schema
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_value DECIMAL(8, 2),
    status VARCHAR(50)
);

CREATE SEQUENCE seq_order_id START WITH 1 INCREMENT BY 1;

-- Sample Data
INSERT INTO orders VALUES (1, 100.00, 'Pending');
INSERT INTO orders VALUES (2, 150.00, 'Processing');
INSERT INTO orders VALUES (3, 120.00, 'Shipped');

-- Automatic detection and adjustment of the sequence
DECLARE
    current_max INT;
BEGIN
    -- Automatically detect the current maximum value in the table
    SELECT MAX(order_id) INTO current_max FROM orders;
    
    -- Automatically adjust the sequence
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_order_id INCREMENT BY ' || (current_max + 1);
    SELECT seq_order_id.NEXTVAL FROM dual;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_order_id INCREMENT BY 1';
END;

Output Explanation:

No explicit output; the sequence is reset for future use.

These examples showcase the complete code with schema and provide insights into the output for each scenario.

Conclusion

Overall, resetting a PL/SQL primary key sequence when it falls out of sync is essential for maintaining data integrity and preventing conflicts with existing records. By dynamically adjusting the sequence increment based on the maximum current value in the table, developers can ensure that new records receive unique and sequential primary key values. Careful consideration of the current state of the table and the sequence is crucial for a successful reset, and this approach helps in aligning sequence values with the actual data in the table.