First, let's analyze an SQL table named subway_station. The table is used by a company that operates subway trains:
CREATE TABLE subway_station ( id integer PRIMARY KEY, line varchar(3), zone integer, name varchar(64) UNIQUE, opened_year integer, passengers_daily integer );
Even though the code above doesn't contain any CREATE INDEX instructions, some indexes are automatically created behind the scenes.
The first index created automatically by the database is a B-tree index on the primary key. If the primary key is a single column, we'll get a single-column index. If the primary key is made up of multiple columns, we'll get a multi-column index.
We've just created a table named invoice for an accounting company:
CREATE TABLE invoice ( id integer PRIMARY KEY, issue_date date, customer_id integer, amount decimal(10, 2), currency char(3) );
We didn't create any indexes on this table. Now, run the template code – it is a special PostgreSQL instruction that lists all indexes for a given table. (Note: This syntax can be different in other database engines.)
As you can see, an index was created automatically for the primary key.
