Fantastic job! Okay, one more note to spice things up: Remember that foreign key columns are columns like any other. As such, they can be used the same way any other column is used. For example, they can be part of a primary key. Confused? Take a look at the ERD:
We've got three tables. The first one, employee, is a table we already know. It stores information about employees in our company.
The second table is called position. This table is where we can keep information about the positions available at our company, such as an accountant, junior associate, etc.
There is also a third table called job_history which is meant to keep track of an employee's position history in the company, because each employee can work at various positions, and each position can be occupied by various employees. Each row in the job_history table corresponds to a single employee (column employee_id) working in a single position (column position_id) from start_date until end_date. There is another column called seq which we added since each employee may work several times in the same position when their role in the company changes.
So, as you can see, the job_history table takes both employee_id from the employee table and position_id from the position table as foreign keys. But these two values, along with the column seq, also create the primary key of the job_history table. Hence, employee_id and position_id are simultaneously foreign keys and parts of the same primary key.
That was a lot of theory, wasn't it? Let's do some practice: create the job_history table. Use the ERD diagram above for help.
Type:
CREATE TABLE job_history (
employee_id integer,
position_id integer,
seq integer,
start_date date NOT NULL,
end_date date NULL,
PRIMARY KEY (employee_id, position_id, seq),
FOREIGN KEY (employee_id)
REFERENCES employee (id),
FOREIGN KEY (position_id)
REFERENCES position (id)
);
