Reference

Auto-incrementing primary keys

Almost every table needs a column that hands out a fresh, unique number for each new row — the OwnerIdentifier, DogIdentifier, and CatIdentifier keys in this book's practice database all work this way.

Because each database implements this a little differently, it isn't shown in the lessons themselves. This page fills that gap with an explanation and a worked example for each of the three databases the book covers.

Already done for you. The setup scripts in the free download already define these incrementing keys — you don't need to write any of this to follow along. It's here for when you want to build tables of your own.

The idea

What "auto-increment" means

When you mark a column as auto-incrementing, the database fills it in automatically every time you insert a row — starting from a value you choose (the seed or start) and going up by a fixed step (usually 1). You simply leave that column out of your INSERT, and the database assigns the next number.

In this book's practice database, most tables start counting at 1, but Dog starts at 500 and Cat at 1000 — a small trick so you can tell at a glance which table an ID came from. We'll use the Dog table (starting at 500) in every example below so you can compare the three approaches directly.

SQL Server — `IDENTITY`

SQL Server uses the IDENTITY(seed, increment) property on a column. IDENTITY(500, 1) means "start at 500 and add 1 each time."

Create the table

CREATE TABLE Dog (
    DogIdentifier INT          NOT NULL IDENTITY(500, 1),
    DogName       VARCHAR(50)  NOT NULL,
    CONSTRAINT PK_Dog PRIMARY KEY (DogIdentifier)
);

Insert a row — leave the identity column out

INSERT INTO Dog (DogName) VALUES ('Rex');
-- Rex is assigned DogIdentifier 500; the next dog gets 501, and so on.

Find the value that was just generated

SELECT SCOPE_IDENTITY();   -- returns the identity created by your last insert

You normally never supply the identity value yourself. If you truly need to, run SET IDENTITY_INSERT Dog ON; first, insert the explicit value, then turn it back off.

MySQL — `AUTO_INCREMENT`

MySQL marks the column with AUTO_INCREMENT. The column must be a key (a primary key is perfect), and a table can have only one auto-incrementing column. To start somewhere other than 1, set the table option AUTO_INCREMENT = 500.

Create the table

CREATE TABLE Dog (
    DogIdentifier INT          NOT NULL AUTO_INCREMENT,
    DogName       VARCHAR(50)  NOT NULL,
    PRIMARY KEY (DogIdentifier)
) AUTO_INCREMENT = 500;

Insert a row — leave the auto-increment column out

INSERT INTO Dog (DogName) VALUES ('Rex');
-- Rex is assigned DogIdentifier 500; the next dog gets 501, and so on.

Find the value that was just generated

SELECT LAST_INSERT_ID();   -- returns the auto-increment from your last insert

You can still supply your own value by including the column in the INSERT; MySQL will continue counting from the highest value used.

PostgreSQL — `GENERATED … AS IDENTITY`

Modern PostgreSQL uses the SQL-standard identity column: GENERATED BY DEFAULT AS IDENTITY, with an optional (START WITH 500). BY DEFAULT lets you supply your own value when you need to; ALWAYS (as in GENERATED ALWAYS AS IDENTITY) tells the database it is always in charge.

Create the table

CREATE TABLE Dog (
    DogIdentifier INT GENERATED BY DEFAULT AS IDENTITY (START WITH 500),
    DogName       VARCHAR(50) NOT NULL,
    PRIMARY KEY (DogIdentifier)
);

Insert a row — leave the identity column out

INSERT INTO Dog (DogName) VALUES ('Rex');
-- Rex is assigned DogIdentifier 500; the next dog gets 501, and so on.

Insert and get the new value back in one step

INSERT INTO Dog (DogName) VALUES ('Rex')
RETURNING DogIdentifier;

You may also see the older SERIAL shorthand — DogIdentifier SERIAL PRIMARY KEY. It still works, but GENERATED … AS IDENTITY is the recommended modern approach.

At a glance

The same idea, three dialects

	SQL Server	MySQL	PostgreSQL
Mark the column	`IDENTITY(500, 1)`	`AUTO_INCREMENT`	`GENERATED BY DEFAULT AS IDENTITY`
Set the start value	The seed in `IDENTITY(500, 1)`	Table option `AUTO_INCREMENT = 500`	`(START WITH 500)`
Get the value just created	`SCOPE_IDENTITY()`	`LAST_INSERT_ID()`	`RETURNING` clause
Supply your own value?	Only with `SET IDENTITY_INSERT ON`	Yes — just include the column	Yes with `BY DEFAULT`; blocked with `ALWAYS`

Auto-incrementing primary keys

What "auto-increment" means

SQL Server — IDENTITY

MySQL — AUTO_INCREMENT

PostgreSQL — GENERATED … AS IDENTITY

The same idea, three dialects

These keys are already set up for you

SQL Server — `IDENTITY`

MySQL — `AUTO_INCREMENT`

PostgreSQL — `GENERATED … AS IDENTITY`