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

Understanding Indexes

Introduction
Automatically-created indexes
3. Indexes on UNIQUE columns
SQL index best practices
Summary

Instruction

Good! Is the index on the primary key the only index created automatically by the database for this table?

CREATE TABLE subway_station (
  id integer PRIMARY KEY,
  line varchar(3),
  zone integer,
  name varchar(64) UNIQUE,
  opened_year integer,
  passengers_daily integer
);

As it turns out, we also get automatically-created indexes for all columns with UNIQUE constraints, no matter if they're single-column or multi-column constraints.

In the case of our table, the database will automatically create an index for the name column.

Exercise

We've modified our table invoice to include the UNIQUE number column:

CREATE TABLE invoice (
  id integer PRIMARY KEY,
  number varchar(18) UNIQUE,
  issue_date date,
  customer_id integer,
  amount decimal(10, 2),
  currency char(3)
);

Again, we didn't create any indexes ourselves so far. Run the template code again to see what indexes are available.

As you can see, two indexes were created automatically: one for the primary key, the other for number.