Creating sequence

📖 Theory

What was the last bill number?

Every bill Meera prints needs a unique number: 1, 2, 3... On paper she checks the last stub and adds one. But now her cousin also prints bills from a second counter, and twice they have both grabbed number 48, two different sales, same bill number. Chaos at audit time.

Manually tracking 'the last number used' breaks the moment more than one person inserts at once. Databases solve it with a sequence: an object whose only job is to hand out the next number, safely, to whoever asks. Never a duplicate.

📖 Theory

The token dispenser at the bank

A busy bank does not ask customers to remember 'who was last'. A token machine dispenses 41, 42, 43... one per press, to whoever presses next. Two people press at nearly the same moment? They still get different tokens, the machine never hands out the same number twice. A sequence is that token dispenser for database numbers: press (ask), receive the next unique value, guaranteed.

⌨️ Practical

A sequence for bill numbers

-- Create the dispenser (Oracle syntax)
CREATE SEQUENCE bill_seq
    START WITH 1
    INCREMENT BY 1;

-- Ask for the next number each time you insert a bill
INSERT INTO bills (bill_no, item)
VALUES (bill_seq.NEXTVAL, 'Sugar');   -- gets 1

INSERT INTO bills (bill_no, item)
VALUES (bill_seq.NEXTVAL, 'Tea');     -- gets 2

-- MySQL does the same with AUTO_INCREMENT on the column:
-- bill_no INT AUTO_INCREMENT PRIMARY KEY

📖 Theory

How it works

CREATE SEQUENCE makes the dispenser; NEXTVAL asks for the next number (and advances the dispenser); CURRVAL peeks at the current one.

Options tune it: START WITH (first number), INCREMENT BY (step, usually 1), MAXVALUE, and CYCLE (restart after the max).

Most databases use sequences to fill primary key IDs automatically, so you never invent them by hand. MySQL wraps the same idea in a column keyword: AUTO_INCREMENT. Different syntax, identical purpose: unique, auto-growing numbers.

🧩 Quiz

Why is a sequence better than Meera manually tracking 'the last bill number' when two counters insert bills at the same time?

1. A sequence guarantees each request gets a unique number, even with simultaneous inserts
2. A sequence prints the bills faster
3. Manual numbering uses less storage
4. A sequence lets two bills share a number safely

💭 Think first

Does a sequence skip numbers?

Meera notices her bill numbers jumped from 47 to 49, number 48 is missing. She fears a bug. Is a gap in a sequence actually a problem? Think about what NEXTVAL guarantees.

⚠️ Watch out

Where marks leak

Thinking a sequence guarantees no gaps, it guarantees uniqueness, not consecutiveness (rollbacks leave gaps). Confusing NEXTVAL (advances and returns the next) with CURRVAL (just peeks). And not knowing the MySQL equivalent, AUTO_INCREMENT on a column. The exam-worthy point is why sequences exist: automatic, unique, concurrency-safe numbering that manual tracking cannot provide.

📖 Theory

You are surrounded by sequences

Every order ID on Amazon, every ticket number on IRCTC, every row ID in the Gri-Learn database, a sequence (or AUTO_INCREMENT) generated it. It is invisible plumbing you now understand. One lesson remains to complete the entire subject: views, saved queries that behave like virtual tables. Next, and last: creating views and how they differ from real tables.

✅ Summary

Key takeaways

• A sequence is a database object that auto-generates unique, increasing numbers.
• Used for primary keys and bill/invoice numbers so you never track the last one manually.
• CREATE SEQUENCE makes it; NEXTVAL gets the next value; CURRVAL peeks at the current.
• It is concurrency-safe: no duplicate numbers even when many users insert at once.
• Gaps can occur (from rollbacks) and are harmless; it guarantees uniqueness, not consecutiveness. MySQL uses AUTO_INCREMENT.
• Memory hook: a bank token dispenser, press for the next unique number.