Sequences and Identity Columns

At a bakery, you take a numbered ticket from a dispenser. You never pick your own number, and nobody gets the same one twice. Oracle has two kinds of ticket dispenser.

Every table needs a primary key, and it's usually a number that the database hands out. MySQL has AUTO_INCREMENT, and SQLite has INTEGER PRIMARY KEY. Oracle takes a different route: first came sequences, separate objects that hand out numbers. Then, in Oracle 12c, came identity columns, which make a sequence for you.

The examples in this lesson create tables, so they need an Oracle account that can create objects. In FreeSQL, that means signing in (see Getting an Oracle Database). The results shown here are what Oracle's documented behavior gives.

Sequences

A sequence is a database object whose only job is to produce a series of numbers. It's not tied to any table, so any number of tables and programs can use it:

CREATE SEQUENCE ticket_seq
    START WITH 100
    INCREMENT BY 1;

You ask a sequence for a number with two special pseudo-columns:

• sequence_name.NEXTVAL advances the sequence, and gives the new number.
• sequence_name.CURRVAL gives the number this session most recently got, without advancing.

SELECT ticket_seq.NEXTVAL FROM dual;
SELECT ticket_seq.NEXTVAL FROM dual;
SELECT ticket_seq.CURRVAL FROM dual;

   NEXTVAL
----------
       100

   NEXTVAL
----------
       101

   CURRVAL
----------
       101

The first NEXTVAL gives the starting number, 100, and the second gives 101. CURRVAL repeats the last one. If you ask for CURRVAL in a session that hasn't yet called NEXTVAL, Oracle refuses with ORA-08002: sequence TICKET_SEQ.CURRVAL is not yet defined in this session. Each session tracks its own last value, which is what makes CURRVAL safe when many people are using the sequence at once.

To use a sequence for a primary key, put NEXTVAL right in the INSERT:

CREATE TABLE tickets (
    id NUMBER PRIMARY KEY,
    holder VARCHAR2(40) NOT NULL
);

INSERT INTO tickets (id, holder) VALUES (ticket_seq.NEXTVAL, 'Ana');
INSERT INTO tickets (id, holder) VALUES (ticket_seq.NEXTVAL, 'Ben');

Every insert takes a fresh number. Options you can set when creating a sequence:

Option	Meaning
START WITH n	the first number (default 1)
INCREMENT BY n	the step (default 1), which can be negative to count down
MAXVALUE n / NOMAXVALUE	the largest number allowed
CYCLE / NOCYCLE	whether to start over when it reaches the maximum
CACHE n / NOCACHE	how many numbers to keep ready in memory (default 20)

Gaps are normal

A sequence never takes numbers back. A number is used up the moment NEXTVAL gives it out, even if:

• the INSERT that used it fails;
• you ROLLBACK the transaction (a sequence isn't part of the transaction);
• the server restarts, which throws away the numbers that were cached in memory but not yet handed out. That's the CACHE setting: keeping 20 numbers ready is faster than storing every one, at the price of possible gaps.

So a table might have IDs 100, 101, 103, 121. That's fine: an ID's only job is to be unique, never to be gap-free. Don't try to make it gap-free, and never use it to count rows.

Identity columns

Since Oracle 12c, you can skip the sequence and let the column generate its own numbers. Say so with GENERATED ... AS IDENTITY:

CREATE TABLE pets (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(30) NOT NULL
);

INSERT INTO pets (name) VALUES ('Bantay');
INSERT INTO pets (name) VALUES ('Mingming');

SELECT id, name FROM pets ORDER BY id;

        ID NAME
---------- --------
         1 Bantay
         2 Mingming

Leave the ID out of your INSERT, and Oracle fills it in: 1, 2, 3, and so on. Behind the scenes, Oracle creates a sequence for you. It's the closest thing to MySQL's AUTO_INCREMENT, and it's what new tables should use.

There are three flavors:

Clause	Behavior
GENERATED ALWAYS AS IDENTITY	Oracle always chooses the number. Trying to insert your own fails.
GENERATED BY DEFAULT AS IDENTITY	Oracle chooses the number unless you supply one.
GENERATED BY DEFAULT ON NULL AS IDENTITY	Like BY DEFAULT, and Oracle also fills in the number when you insert an explicit NULL.

With ALWAYS, this fails:

INSERT INTO pets (id, name) VALUES (99, 'Sneaky');

ORA-32795: cannot insert into a generated always identity column

That's a useful guard: nobody can make a duplicate or out-of-order ID by accident. If you need to load rows with existing IDs, such as data copied from another database, use BY DEFAULT for that table. You can also set the start and the step, exactly as you would with a sequence:

CREATE TABLE invoices (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1) PRIMARY KEY,
    customer VARCHAR2(40)
);

Sequence or identity?

• For a plain primary key on one table, use an identity column: less to write, and the numbers can't be misused.
• Use a sequence when several tables share one numbering (invoices and credit notes drawing from one series), when the number is used somewhere other than a column, or in older databases from before 12c, where it's the only choice. You'll also meet sequences plus triggers in a great deal of older code.
• A third way, also from 12c, connects the two: a column can default to a sequence, with id NUMBER DEFAULT ticket_seq.NEXTVAL PRIMARY KEY.

Getting the new ID back

After an insert, you often need the ID that was just generated, for example to add related rows. In Oracle, the tool for this is the RETURNING ... INTO clause. It's most often used in PL/SQL, which you'll meet in the next chapter:

DECLARE
    v_new_id pets.id%TYPE;
BEGIN
    INSERT INTO pets (name) VALUES ('Bugs')
    RETURNING id INTO v_new_id;
    DBMS_OUTPUT.PUT_LINE('New pet id: ' || v_new_id);
END;
/

New pet id: 3

This is the safest way to learn the new ID, since it belongs to this insert, even when other sessions are inserting at the same moment. (Don't use SELECT MAX(id): someone else may have inserted in between.)

Try it

A raffle uses a sequence for ticket numbers, and an identity column for the prizes. Predict what each query returns.

Running Oracle SQL

Syntaxia can't run Oracle Database in the browser, because it needs a real Oracle server. To try this yourself, use FreeSQL while signed in, or your own Oracle installation.

Check your prediction

FIRST_TICKET
------------
          10

SECOND_TICKET
-------------
           15

THIRD_TICKET
------------
          20

CURRENT_TICKET
--------------
            20

ORA-32795: cannot insert into a generated always identity column

        ID NAME
---------- --------
         3 Umbrella

The sequence starts at 10 and steps by 5: 10, 15, 20, and CURRVAL repeats 20. The first two prizes get IDs 1 and 2. The third insert is refused, because the column is ALWAYS. Then ROLLBACK undoes the first two inserts (only rows are rolled back). The identity's numbers are not, so the umbrella gets 3, not 1: a gap, just as the lesson promised. (In a script, the failed insert doesn't stop the later statements. Your tool may show a message that the first two rows were inserted.)

Try it yourself

1. Change the prizes table to GENERATED BY DEFAULT AS IDENTITY. Does the Mystery box insert work now? What ID does the next prize get?
2. Create a sequence that counts down from 100 by 10. What are the first three NEXTVAL values?
3. Ask for raffle_seq.CURRVAL in a brand-new session. What error do you get, and why?

Check your understanding

What does sequence_name.NEXTVAL do?

Shows the last number without changing it

Deletes the sequence

Resets it to the start

Advances the sequence and returns the new number

A row is inserted with ticket_seq.NEXTVAL, then rolled back. What happens to that number?

It is given out again

It is used up for good, leaving a gap

The sequence resets

The rollback fails

Which identity clause lets you supply your own ID when you want, and lets Oracle choose otherwise?

GENERATED ALWAYS AS IDENTITY

IDENTITY ONLY

GENERATED BY DEFAULT AS IDENTITY

AUTO_INCREMENT

Up next

Sometimes you want to insert a row, or update it if it's already there. Oracle has a powerful statement for that, in Upserts with MERGE.