Primary Key and Auto-increment

1. Sequences

All databases will have the option of an auto-incrementing column to use as the primary key column in a table. In PostgreSQL, this is done with a SEQUENCE, which is a special type of object that creates a sequence of integers. It is important to know the order of the numbers, as the sequences of 1, 2, 3, 4, 5 and 5, 4, 3, 2, 1 would be different sequences.

Sequences can have a few common parameters:

• START: The value that the sequence starts with. The default is to start with 1.
• INCREMENT: The value that should be added to the current sequence value to create a new value.
• MINVALUE: The minimum value that is set to a sequence. The default is 1.
• MAXVALUE: The maximum value that is set to a sequence. The default maximum value is the maximum value of the data type of the sequence.

CREATE SEQUENCE <sequence_name>
[parameters];

For example, if we wanted to create a sequence named mysequence and have it start with 10, and increment it by 10, we would do the following:

CREATE SEQUENCE mysequence
START 10
INCREMENT 10;

If we wanted to get the next value from the sequence, we can use the nextval function like this:

SELECT nextval('mysequence');

You’ll notice that the first value is set to 10. If we run the same statement again, it’ll increment the value by 10:

2. Using the Data Type SERIAL

A sequence can be automatically added to a table using the data type SERIAL. It is an important pseudo-type as it simplifies much of the complexity of creating the sequence and incrementing it. By assigning the SERIAL pseudo-type to a table, the database will do the following:

• Creates a sequence object and sets the next value of the sequence as the default value for the column.
• Adds a NOT NULL constraint to the column since the sequence should always generate an integer which is always not null.
• Assigns the owner of the sequence to the column in the table, so if the column or the table containing it is dropped (removed), the sequence is also removed.

CREATE TABLE contact( contact_id SERIAL, username VARCHAR(50), password VARCHAR(50) );

Behind the scenes, it is the same as the database doing this:

CREATE SEQUENCE contact_contact_id_seq;

CREATE TABLE contact(
        contact_id integer NOT NULL DEFAULT nextval(contact_contact_id_seq),
        username VARCHAR(50),
        password VARCHAR(50)
);

ALTER SEQUENCE contact_contact_id_seq
OWNED BY contact.contact_id;

This looks complex and has some extra commands we haven’t reviewed yet, but it follows the structure as we described above:

• Creates a sequence with a unique specific name behind the scenes.
• Creates the table with the contact_id set as an integer with the NOT NULL constraint. The default value is set to the next value of the sequence.
• Alters the sequence to set the owner to the contact_id column in the contact table.

CREATE TABLE contact(
        contact_id SERIAL PRIMARY KEY,
        username VARCHAR(50),
        password VARCHAR(50)
);

We will explore inserting into a table with the SERIAL primary key in a later tutorial.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.