Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Data Types in SQL

Introduction
Identity columns
Sequences
Summary

Instruction

Good. Let's now talk about 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've provided us with tools to automatically number our rows! Take a look:

CREATE TABLE movie (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title varchar(64),
  year int,
  genre varchar(20)
);

As you can see, we've added the following keywords after the type of the first column: GENERATED ALWAYS AS IDENTITY. Thanks to this, the database will know that it should calculate consecutive numbers for this column on its own.

Note that you can have the database generate values automatically even in non-primary keys.

Exercise

Let's try to add a new row to the table movie. Run the template code. What do you think will happen?

Success! Look at the table – the value for the column id has been generated automatically. The database took care of the numbering. Isn't that great?