Primary keys - the basics
Single-column primary keys
Multicolumn primary keys
Auto-increment columns and sequences
23. The AUTO_INCREMENT keyword
Revision

Instruction

Okay. Note that in some databases, you will encounter the keyword AUTO_INCREMENT instead of IDENTITY. The AUTO_INCREMENT is not used as a data type. It is an additional keyword that you put after the name of the type. The table movie from an earlier exercise could be created like this:

CREATE TABLE movie (
    id INTEGER AUTO_INCREMENT (3,5),
    title varchar(64),
    year int,
    genre varchar(20)
);

If you omit the AUTO_INCREMENT parameters, then the auto incremening numbers start with 1 and increment by 1. Like here:

CREATE TABLE movie (
    id INTEGER AUTO_INCREMENT,
    title varchar(64),
    year int,
    genre varchar(20)
);

Exercise

Create the table cinema again, but use the keyword AUTO_INCREMENT this time.

Include the following columns:

  • cinema_id - integer with consecutive 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 INTEGER AUTO_INCREMENT PRIMARY KEY,
    name varchar(30),
    city varchar(30)
); 

Console

Code editor

Result

TableConsole