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

Excellent. There is actually one more thing about GENERATED ... AS IDENTITY that you need to know. You can change its behavior! You do not need to start counting from 1, nor do you need to add 1 to every new value. Take a look:

CREATE TABLE movie (
  id integer GENERATED ALWAYS AS IDENTITY (START WITH 5 INCREMENT BY 10) PRIMARY KEY,
  title varchar(64),
  year int,
  genre varchar(20)
);

After the keyword IDENTITY, we put two values in the parentheses. The first one (START WITH) tells the database about the starting point value. In this case, the first row will get the number 5. The second value (INCREMENT BY) informs the database how it should calculate further values. In this case, we're asking it to add 10 each time. So, after 5, we'll have 15, 25, 35...

Exercise

We've created a table named theater for you using the following code:

CREATE TABLE theater (
  theater_id integer GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 2) PRIMARY KEY,
  name varchar(30),
  city varchar(30)
);

As you can see, only odd-numbered identifiers should be generated. Your task is to insert some rows and observe if that's true.

  1. Add a theater named Paramount in the city of Seattle. What ID has been generated for this row?
  2. Add a theater named Moore in the same city (Seattle). What ID can you see for this row?

When you're done, click Next exercise.