Primary keys - the basics
Single-column primary keys
Multicolumn primary keys
Auto-increment columns and sequences
18. New keyword IDENTITY
Revision

Instruction

Excellent! Let's now get back to the single-column primary keys with integer numbers. They are the most frequent choice in professional databases. Because of their popularity, database system creators have decided to make our lives easier. They provide us with tools to automatically number our rows! Take a look:

CREATE TABLE movie (
    id IDENTITY PRIMARY KEY,
    title varchar(64),
    year int,
    genre verchar(20)
);

As you can see, we've added a new keyword IDENTITY after the name of the column. Thanks to it, the database will know that it should calculate the consecutive numbers for this column. IDENTITY works like a data type, so we write it instead of our data type (like int).

The keyword IDENTITY is usually used for primary keys, but it may not be so. You can have a column which is not a primary key and still have your database generate the values automatically.

Exercise

Try to add a new row to the table movie. It's a movie called Birdman from 2014, the genre is comedy-drama. Do NOT specify the id column. What do you think will happen?

Stuck? Here's a hint!

Type

INSERT INTO movie (title, year, genre) VALUES ('Birdman', 2014, 'comedy-drama');

Console

Code editor

Result

TableConsole