How to Auto Increment Id in Oracle Database - Onlyxcodes - onlyxcodes

Thursday, 19 April 2018

How to Auto Increment Id in Oracle Database - Onlyxcodes

Hello SQL People, today you will see SQL codes of how to auto increment id in Oracle database. The Oracle SQL command does not provide Id facility for auto-increment. To the compulsory type Id 1, 2, 3 ... N for Data insertion. 

This tutorial solves this problem using the technique of trigger and sequence to automatically expand column Id with each row record inserted.

Let’s dive into the tutorial.

How to Auto Increment Id in Oracle Database

Table Content

1. Create Table

2. Create Sequence

    2.1 What is Sequence?

    2.2 Pros of Sequence

    2.3 CURRVAL and NEXTVAL pseudo column

3. Create Trigger

    3.1 What is Trigger?

    3.2 Pros of Trigger

    3.3 Types of Trigger

4. Output


Note:- Here I've to use oracle 11g database Download 

1. Create Table

Create the table first, and type its mobile name. Below SQL codes execute your SQL oracle command prompt to be successful in making a table.

I assign the primary key in the mobile_id column here because in this column we have auto_increment number with each row record inserted.

  SQL> create table mobile(
  2  mobile_id number(5) primary key,
  3  mobile_name varchar2(30)
     );

create table in oracle SQL command line

2. Create Sequence

Before creating sequence let’s see about of sequence.

2.1 What is Sequence


In Oracle, a sequence is an object used to create a number sequence. That can be useful when a unique number is needed to act as a primary key.

2.2 Pros of Sequence:-


1. Using sequence to automatically given primary key values.

2. When one user creates a sequence value, the user will continue to access that value independently of whether another user increases the sequence.

3. Another user never gets the sequence number that another user creates.

4. Apart from that, a single user does not know that other users draw from the same sequence.

5. When two users raise the same sequence simultaneously then the sequence numbers that each user obtains have holes because the other user creates sequence numbers.

6. After a sequence is generated, CURRVAL and NEXTVAL pseudo column can access its values in the SQL statements.

2.3 CURRVAL and NEXTVAL Pseudo Column:-


CURRVAL: - The pseudo column returns the sequence current value.

NEXTVAL: - This pseudo column increases the sequence and the new value is returned.

syntax of sequence: -


create sequence sequence_name; 

create sequence: -


 create sequence mobile_sequence;

create sequence in oracle SQL command line

3. Create Trigger

Last third creat trigger and trigger codes include in sequence for helping originate auto-increment id.

3.1 What is Trigger?


TRIGGERS are stored programs that are automatically fired by Oracle machine when DML statements such as insert, update, delete events occur on the table. The code to be executed in the case of a trigger may be set according to the requirement.

3.2 Pros of Trigger: -


1. Compare to procedure triggers invoke your statement implicitly. But the procedure invokes your statement explicitly.

2. Your declare operation guaranteed to perform by the trigger.

3. Save your database event using the trigger, such as shutdown, startup logon, logoff.

4. Enforcing referential integrity

5. Event logging and storing information on table access

6. Auditing

7. Synchronous replication of tables

8. Imposing security authorizations

9. Preventing invalid transactions

3.3 Types of Trigger  


Timing base: -


BEFORE –  That applies before the mentioned event has caused.

AFTER –  That applies after the mentioned event has caused.

Statement level and Row level base: -


STATEMENT level – For the defined statement of the event, it applies once.

FOR EACH ROW – For each record that has been impacted in the specified event it burns.

Event base: -


DML (Data Manipulation Language) – That applies when defined as a DML event (INSERT or UPDATE or DELETE).

DDL (Data Definition Language) – That will applies when you define the (DDL event CREATE or ALTER).

DATABASE – That applies when you define the database event (LOGON or LOGOFF or STARTUP or SHUTDOWN).

The simple syntax of trigger for entire tutorial use:-


CREATE OR REPLACE TRIGGER auto trigger_name
 BEFORE INSERT ON table_name
 FOR EACH ROW
BEGIN 

Create triggers according to the above syntax: -


CREATE OR REPLACE TRIGGER mobile_trigger
 BEFORE INSERT ON mobile
 FOR EACH ROW
BEGIN
//sequence code here 

Exact end of trigger syntax call "BEGIN", implement sequence code for help auto_increment id.

CREATE OR REPLACE TRIGGER mobile_trigger
 BEFORE INSERT ON mobile
 FOR EACH ROW
BEGIN
 SELECT mobile_sequence.nextval
 INTO :new.mobile_id
 FROM dual;
END;  

create trigger in oracle SQL command line

I clarify steps above to create a table, sequence, and trigger I'm pasting full table, sequence, and trigger SQL codes below.

SQL> create table mobile(
  2  mobile_id number(5) primary key,
  3  mobile_name varchar2(20)
     );

SQL> create sequence mobile_sequence;

SQL> create trigger mobile_trigger
  2    before insert on mobile
  3    for each row
  4  begin
  5    select mobile_sequence.nextval
  6       into:new.mobile_id
  7       from dual;
  8  end;
  9   /  

create a table, sequence, and trigger full SQL codes

OK, all SQL commands are perfectly created, let's understand the flow in mobile_id column how they are auto_increment numbers.

Explanation:

Declared "INSERT" and "BEFORE" event trigger, that runs before inserting the record into a mobile table.

And the level based event "FOR EACH ROW" trigger affects every row of columns.

We SELECT is the next incremental value from mobile_sequence recently created.

Pseudo column .nextval increases the value that can be retrieved from the sequence.

Into: new - this statement inserts the mobile table new record in the .mobile_id field mentioned.

4. Output

All commands are perfectly let's tested.

I have three records inserted into the mobile table but I don't have a mobile_id field. Below insert query command execute your oracle SQL command.

  insert into mobile(mobile_name)values('iphone x');
  insert into mobile(mobile_name)values('samsung j2');
  insert into mobile(mobile_name)values('mi note 4');

type SQL insert query in oracle SQL command line

Showing records by select query command.

select * from mobile;

type SQL select query in oracle SQL command line

Learn More:


1 comment: