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

Creating Tables in SQL

Primary keys - the basics
Single-column primary keys
Multicolumn primary keys
Auto-increment columns and sequences
Revision

Instruction

Excellent. There is actually one more thing about 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 IDENTITY(3,5),
    title varchar(64),
    year int,
    genre varchar(20)
);

After the keyword IDENTITY, we put two values in the parentheses. The first one informs about the starting point value. In this case, the first row will obtain the number 3. The second value informs the database how it should calculate further values. In this case we're asking to add 5 each time. So, after 3, there come 8, 13, 18, ...

Exercise

We want to create a similar table cinema, with one exception: we want to start with cinema_id 1 and only have odd numbers as ids.

Create a table called cinema with the following columns:

  • cinema_id - integer with odd numbers automatically generated, it's also the primary key,
  • name - up to 30 characters,
  • city - up to 30 characters.

Stuck? Here's a hint!

Type

CREATE TABLE cinema (
   cinema_id IDENTITY(1,2) PRIMARY KEY,
   name varchar(30),
   city varchar(30)
);