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.
What is Auto-Increment 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.
Table of Content
1. Using Sequences
Sequences are objects in PL/SQL that generate unique numeric values. They are particularly useful for auto-incrementing IDs in tables. Here is how you can create and use sequences to achieve auto-incrementing IDs:
Syntax
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
Example of Using Sequences:
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:
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 of Using Triggers:
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:
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. While sequences offer simplicity and ease of use, triggers provide greater flexibility and control over the insertion process.
AUTO_INCREMENT in PL/SQL - FAQs
How do you create a sequence for auto-incrementing IDs in PL/SQL?
To create a sequence:
CREATE SEQUENCE sequence_name
MINVALUE 1
START WITH 1
INCREMENT BY 1;
Can you use sequences without triggers to auto-increment IDs?
Yes, sequences can be used directly in INSERT statements without the need for triggers by calling sequence_name.NEXTVAL for the ID column.
Which method is better: using sequences directly or using triggers?
The choice depends on your requirements:
- Using sequences directly is simpler and sufficient for many cases where you control INSERT statements.
- Using triggers provides more control and is useful when you want auto-increment behavior regardless of how rows are inserted.
Can you use both sequences and triggers together?
Yes, you can use a combination of sequences and triggers to auto-increment IDs automatically whenever a row is inserted into a table, ensuring consistency and data integrity.
