How to Create id With AUTO_INCREMENT in PL/SQL?

PL/SQL, short for Procedural Language/Structured Query Language, combines SQL capabilities with procedural programming. It supports variable declaration, control flow structures, functions, records, cursors, procedures, and triggers.

PL/SQL features a block structure with optional sections for variable declarations and exception handling. Its execution section handles SQL queries seamlessly. Overall, PL/SQL offers a robust environment for developing database-centric applications with enhanced control and error-handling capabilities.

AUTO_INCREMENT id in Pl/SQL

Sometimes while creating a table we do not have a unique identifier within the table hence we face difficulty in choosing the Primary Key. To resolve such an issue, we’ve to provide unique keys to every record manually, but this is often also a tedious task. So we can use the Auto Increment feature that automatically generates a numerical Primary key value for every new record inserted. To use the auto-increment feature we have to make the auto-increment field with the sequence object which successively generates several sequences.

Syntax:

CREATE SEQUENCE sequence_name MINVALUE 1 START WITH 1

INCREMENT BY 1

CACHE 10;

In the above syntax,

• sequence_name: Refers to the name of the sequence object we would like to produce.
• START WITH: Followed by the starting value that we would like to use. Here we have provided 1 as the starting value.
• INCREMENT BY: Followed by the value by which you want to increment. Here we want to increase the subsequent keys by 1.
• CACHE: Followed by the maximum number of values to be stored for faster access.

How to Create Auto Incrementing IDs in PL/SQL

When working with PL/SQL, generating unique IDs for database records can be cumbersome. To streamline this process, two methods are commonly employed: using sequences or triggers.

• Using Sequences
• Using Trigger

1. Using Sequences

Sequences are objects in PL/SQL that generate unique numeric values. They are particularly useful for auto-incrementing IDs in tables.

Syntax:

CREATE SEQUENCE sequence_name

START WITH 1

INCREMENT BY 1;

Example:

SET  SERVEROUTPUT ON;

CREATE SEQUENCE player_seq MINVALUE 1 START WITH 1
  INCREMENT BY 1  NOCACHE ; 

CREATE TABLE PLAYER1(ID NUMBER PRIMARY KEY , NAME VARCHAR(30), SCORE NUMBER );

INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Ellyse Perry', 347);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Smriti Mandhana', 300);
INSERT INTO PLAYER1(ID, NAME, SCORE) VALUES (player_seq.nextval, 'Shafali Verma', 309);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Deepti Sharma', 295);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Harmanpreet Kaur', 268);

SELECT * FROM PLAYER1;

Output:

Auto Increment in PLSQL

Explanation:

• SET SERVEROUTPUT ON; is used to display the output generated from the code.
• player_seq is the sequence created with a start value of 1 and with an increment value of 1.
• In the Insert query player_seq.nextval is used to auto increment the id.

2. Using Trigger to AUTO_INCREMENT id in Pl/SQL

PL/SQL triggers are block structures and predefined programs invoked automatically when some event occurs. They are stored in the database and invoked repeatedly in a particular scenario.To auto increment the ID, we will use BEFORE conditional trigger so that it is activated as the user try to insert the row into the table.

Syntax:

CREATE SEQUENCE sequence_name

START WITH 1

INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trigger_name

BEFORE INSERT ON table_name

FOR EACH ROW

BEGIN

SELECT sequence_name.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

• Before Conditional trigger is defined to activate as the new rows are inserted in the table.
• sequence_name.NEXTVAL INTO :NEW.ID is used to auto-increment the id.
• FROM DUAL is a automatically created table used to execute function or operation which do not depend on any table data.

Example:

SET SERVEROUTPUT ON;

CREATE SEQUENCE play_id START WITH 1 INCREMENT BY 1;

CREATE TABLE PLAYER1(ID NUMBER PRIMARY KEY , NAME VARCHAR(30), SCORE NUMBER );

CREATE OR REPLACE TRIGGER id_trigger
BEFORE INSERT ON PLAYER1
FOR EACH ROW
BEGIN
    SELECT play_id.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Ellyse Perry', 347);
INSERT INTO PLAYER1 ( NAME, SCORE) VALUES ('Smriti Mandhana', 300);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Shafali Verma', 309);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Deepti Sharma', 295);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Harmanpreet Kaur', 268);

SELECT * FROM PLAYER1;

Output:

Auto-Increment in PLSQL

Explanation:

• SET SERVEROUTPUT ON; is used to display the output generated from the code.
• play_id is the sequence created with a start value of 1 and with an increment value of 1.
• Player1 table is created with ID,NAME nad Score field.
• Trigger is defined with before insert effect to auto increment the id .
• As the rows are inserted ,id_trigger is triggered and the id field is auto incremented.

Conclusion

In conclusion, we explored two methods to auto-increment IDs in PL/SQL tables: using sequences and triggers. Both approaches offer efficient ways to manage unique identifiers and streamline database operations. Whether you prefer the simplicity of sequences or the flexibility of triggers, PL/SQL provides powerful tools for ID management in database tables.