50

Why would we create a sequence even if there is a primary key?

Nickz2
  • 93
  • 2
  • 3
  • 14
Matrix
  • 7,477
  • 14
  • 66
  • 97

3 Answers3

61

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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
  • If there is no transaction isolation, then how could it be used? – rapt Oct 25 '11 at 00:10
  • 6
    There is no transaction isolation for the generation of the ids. It always returns the next value of the sequence. Transaction isolation would mean that parallel transaction would either get the same value or one needs to wait for the other transaction to commit before it can get the next value. This is neither required nor useful. – Stefan Steinegger Oct 26 '11 at 11:18
  • 1
    How does it handle deletion of a particular record? – Prashant Abdare Jan 13 '19 at 04:32
  • 3
    @Prashant: The sequence itself is not related to the records at all. It's just a counter that is incremented when you need an ID. It doesn't even know what the ID is used for. It doesn't "handle" deletion of records at all. – Stefan Steinegger Jan 14 '19 at 08:24
10

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

It's different from a serial or auto_incremement primary key in the sense that:

  • It is an actual database object (you need to create it):

    sql> create sequence NAME_OF_YOUR_SEQUENCE;

  • You could assign independent permissions to it, to different database users:

    sql> grant select on NAME_OF_YOUR_SEQUENCE to NAME_OF_YOUR_USER;

  • You can use to have a unique number that is different among several tables (not just one). Say you have four tables with numeric primary keys, and you want unique numbers among those four tables. You could use a sequence for that, without having to worry about implementing locking mechanisms to do it 'by hand'.

  • You can change its number to any value you want with alter sequence

  • You can cycle through its numbers

    sql> create sequence NAME_OF_YOUR_SEQUENCE maxvalue 1500 cycle;

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • 1
    So how is a sequence different from an auto_increment primary key? Or is that actually a sequence? – Blaise Feb 01 '18 at 09:22
8

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).

Andrzej Doyle
  • 102,507
  • 33
  • 189
  • 228