Excellent! Let's extend our database model a bit – we'll now have a separate table for subway lines:
CREATE TABLE subway_line ( id integer PRIMARY KEY, name varchar(3) ); CREATE TABLE subway_station ( id integer PRIMARY KEY, line_id integer, zone integer, name varchar(64) UNIQUE, opened_year integer, passengers_daily integer, FOREIGN KEY (line_id) REFERENCES subway_line(id) );
Our table subway_station now contains a foreign key that references the column id in the table subway_line. Will the database engine create an index for the foreign key line_id in the subway_station table?
In most databases, the answer is no. An index on a foreign key is not created automatically in SQL Server, PostgreSQL, or Oracle. It is, however, automatically created when you use MySQL with the InnoDB engine.
If you need an index on a foreign key and your database doesn't create it for you, you can create it yourself. It's just like any other index:
CREATE INDEX line_id_fkey_index ON subway_station(line_id);
We've modified the accounting company's database model. It now contains two tables:
CREATE TABLE customer ( id integer PRIMARY KEY, name varchar(64) ); CREATE TABLE invoice ( id integer PRIMARY KEY, number varchar(18) UNIQUE, issue_date date, customer_id integer, amount decimal(10, 2), currency char(3), FOREIGN KEY (customer_id) REFERENCES customer(id) );
Create an index named customer_fkey_index on the foreign key customer_id in the table invoice.
