what is sequence (Database) ? When we need it ?

Question

why we create a sequence even if primary key is there ?

Answer 1

Sequence will allow you to populate primary key with a unique, serialized number.

Answer 2

The primary key is a column in a table.

The primary key needs a unique value, which needs to come from somewhere.

The sequence is a feature by some database products which just creates unique values. It just increments a value and returns it. The special thing about it is: there is no transaction isolation, so several transactions can not get the same value, the incrementation is also not rolled back. Without a database sequence it is very hard to generate unique incrementing numbers.

Other database products support columns that are automatically initialized with a incrementing number.

There are other means to create unique values for the primary keys, for instance Guids.

Answer 3

The primary key is (in technical terms) merely an index that enforces uniqueness (as well as speeding query performance). There's some semantic information there along that being the "key" for the entity the row describes, but that's it.

A sequence is a different entity entirely; it exists separate from tables (like a stored procedure would) and can be called to yield sequential numbers.

The two are often used together, to generate automatic primary keys for entities that have no sensible "native" keys. But they are two separate concepts; you can have tables where the primary key is explicitly populated during an insert, and you can have sequences that are used to populate non-PK columns (or even used imperatively during a stored procedure, distinct from inserting records).