Why you should use sequences instead of setting primary key by manual
As you can guess, our subject is based on INSERT operations on databases like Oracle.
Many developers develop a lot of PL/SQL codes by working at procedures, functions, triggers or packages. In these objects, CRUD operations take a significant place. However, you can also run these operations just on a SQL Editor for sure. The difference is that you can quickly fix the problems on editor because you do not need to debug or deploy an object such as packages.
Almost all tables have a primary key or unique index. To assign the key -mostly named as id- , 2 ways are used commonly:
- Getting the next value from a sequence object
- By manual; such as getting the max id value from the table by SELECT query and incrementing it by 1. Here is an example:
SELECT max(id)
INTO new_id
FROM tableA;new_id := new_id + 1;INSERT INTO tableA(id, columns..) VALUES(new_id, column_values…);
As you can see, it is a simple code block and in normal conditions it works without any problem. But, what if the system has more than one instance and they call the block heavily? You probably get the unique constraint error occasionally:
ORA-00001: unique constraint(PK_TABLEA) violated ORA-06512: at …
PL/SQL is not a multithread language. I is not easy to synchronize your code block. Oracle can lock the tables to prevent modify data at the same time, but pl/sql code block may work parallel. The above example shows that, 2 instances may generate same id before they arrive the INSERT line. One of the ways to solve this problem is very simple: using a sequence to generate the id key like:
INSERT INTO tableA(id, columns..) VALUES(Seq_TableA_id.nextval, column_values…);Now every instance can generate id simultaneously without any conflict. You can also call the sequence before inserting and assign it to variable:
new_id = Seq_TableA_id.nextval;This either does not cause any problem because every time nextval is called sequence already has a new current value.
I hope this easy practice may help some of you.
