Auto-Increment Columns: FAQ

What is an auto-increment column?

An auto-increment column is one of which the value increments automatically each time a row is inserted into the table.

What is the use of auto-increment columns?

An auto-increment column usually serves as the primary key or unique identifier for each row of the table. Since the value automatically increments with each insert, the column guarantees that each row has a unique value associated with it.

An auto-increment column also gives useful information about the sequence of transactions. The higher the column value, the later the row was entered into the table. This can be useful for finding data such as

– the newest records in the tablr
– last N rows in the table, such as last 100 rows inserted
– in a multi-user environment, which user’s insert happened earlier

How to create an auto-increment column?

1. Create the table with the column for auto-increment.
2. Create a sequence to map with the auto-increment column.
3. Create a database trigger on the table, to populate the auto-increment column with value from the sequence. The trigger should fire before insert of each row on the table.

A step-by-step illustration of creating auto-increment column

1. Create the table with the column for auto-increment. In this case study, the table is called t_autoinc, with four columns. Column ‘a’, the primary key, will be used for auto-increment.

SQL> create table t_autoinc
2   ( a NUMBER not null primary key, -- Column for auto-increment
3       b NUMBER,
4       c NUMBER,
5       d NUMBER
6   );

Table created.

2. Create a sequence to map with the auto-increment column.

SQL> -- Create sequence of unique numbers, to populate t_autoinc.a
SQL> create sequence s_autoinc
2   start with 1
3   increment by 1;

Sequence created.

3. Create a database trigger on the table, to populate the auto-increment column from the sequence. The trigger fires before insert for each row on t_autoinc. It will take the next value from sequene s_autoinc, and place it into column ‘a’ before the INSERT operation on the table is completed.

SQL> create trigger trg_autoinc
2   before insert on t_autoinc
3   for each row
4   begin
5       -- Fetch next value of sequence into t_autoinc.a
6       select s_autoinc.nextval into :new.a from dual;
7   end;
8   /

Trigger created.

That completes the infrastructure of the table.   Now, suppose you want to insert data of this kind in the table:

Column ‘a’ is the auto-increment column, and so we should not need to enter it explicitly into the table.

The inserts will be written as below:

SQL> -- Table status before inserts
SQL> select * from t_autoinc;

no rows selected

SQL> -- Insert statements.
SQL> -- Notice that value for column 'a' is not mentioned, it'll get populated in the background.
SQL>
SQL> insert into t_autoinc (b,c,d)
2   values (100, 130, 130);

1 row created.

SQL> insert into t_autoinc (b,c,d)
2   values (150, 200, 240);

1 row created.

SQL> -- Table status after inserts
SQL> select * from t_autoinc;

A                   B                   C                   D
---------- ---------- ---------- ----------
1               100               130               130
2               150               200               240

SQL>

Voila! Column ‘a’ has got auto-incremented.

For Further Reading

You might also want to read about how to replace duplicates in ID column with unique values and how to know the value of the auto-increment column during INSERT.