Introduction
Foreign keys
Multi-column Foreign Keys
Instruction
Excellent! Okay, we promised to teach you how to create multiple foreign keys in a single table, so here we go:
CREATE TABLE table1 (
column1 integer NOT NULL,
column2 integer NOT NULL,
FOREIGN KEY(column1)
REFERENCES table2(column3),
FOREIGN KEY(column2)
REFERENCES table3(column4)
);
As you can see, if you want to create another foreign key, you just need to repeat the whole FOREIGN KEY construction after a comma. Do not provide all the column names inside one pair of parentheses, as this won't create multiple foreign keys but a single multi-column foreign key instead!
Exercise
Let's create the department table once again – this time with two separate foreign keys: one on column manager_id pointing to employee.id and the other on two columns: office_floor (referring to office.floor) and office_building_name (referring to office.building_name). Use the ERD below to help you:
Stuck? Here's a hint!
Type:
CREATE TABLE department (
id integer PRIMARY KEY,
name varchar(1000) NOT NULL,
manager_id int NOT NULL,
office_floor int NOT NULL,
office_building_name varchar(255) NOT NULL,
FOREIGN KEY (office_floor, office_building_name)
REFERENCES office (floor, building_name),
FOREIGN KEY (manager_id)
REFERENCES employee(id)
);
