Create table with sequence.nextval in Oracle

Elevate your game with Identity columns in 12c

In Oracle 12c, creating a new table with an auto-incrementing ID column can be done using the IDENTITY column feature. It's simple, efficient, and crystal clear:

CREATE TABLE new_table (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY, // Pass the baton to IDENTITY, sit back, and relax!
    other_columns ...
);


This is the gig of IDENTITY columns in Oracle 12c: to auto-increment sequence values 🚀

For old-times sake: Triggers and Sequences in Oracle 11g

If you're a fan of Oracle 11g or prior versions, you'd have to employ a trigger for sequence auto-increment, but not without yak shaving:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SELECT seq_name.NEXTVAL INTO :new.id FROM dual; // Sequence, do your thing!
END;


Choosing triggers brings a certain degree of control, but at the cost of simplicity and performance.

The Anatomy of sequence.nextval

Let's do a visual walk-through on how sequence.nextval works; you'll see it's like running a highly-efficient and organized event:

Imagine a sequence as a disciplined line of people waiting for a happening event 🎬:

Each person gets a unique ticket (🎟️) with a number:

CREATE TABLE new_table (
    ID NUMBER DEFAULT my_sequence.NEXTVAL, // The hand that tirelessly gives out 🎟️ after 🎟️!
    other_columns ...
);


As each new row is added, it's like:

👤 → 🎟️1 → 👤 → 🎟️2 → 👤 → 🎟️3 → ... (and so on, each with their unique ID)

Everyone's happy, all thanks to sequence.nextval✨

SYS_GUID to the rescue!

To generate a unique ID in Oracle, the SYS_GUID() function is a formidable option:

CREATE TABLE unique_table (
  id RAW(16) DEFAULT SYS_GUID(), // Tadaa, unique id! No two alike, just like snowflakes.
  other_columns ...
);


The ID created by SYS_GUID() has such a unique value that even a parallel universe won't have the same!

CURRVAL & NEXTVAL: A tale of two sequences

When setting up your sequences, keep in mind two crucial properties: CURRVAL and NEXTVAL. The former returns your current sequence number, while the latter anticipates the next number in the sequence. Voila!

Considerations, choices, and cautions

Choosing the right autofill method in Oracle is a bit like playing chess - it's strategic! Here are some considerations:

• IDENTITY columns are a smooth ride but low on personalization.
• Sequences offer flexibility, but slightly compromise execution time.
• Triggers pose control, albeit with some added complexity.
• SYS_GUID() stands out for uniqueness, but to some, it may seem like using a sledgehammer to crack a nut.

Consult Oracle's documentation and community resources like Oracle-base to make the best choice for your stage of the game.