Foreign KeyOptional for Informatics Practices

Every key so far worked inside one table. A foreign key is the first one that reaches across to another — and it is what finally makes a relational database relational in the everyday sense of the word.

Who this page is for

The foreign key is named in the Computer Science (083) Class 12 syllabus, under the relational data model. It is not in the Informatics Practices (065) syllabus, so IP students can treat this page as background reading. It is short, and it explains why the two-table join later on works at all.

1The problem it solves

Suppose you record which teacher teaches each subject, all in one table:

one big table — the same teacher typed again and again

code	subject	teacher	designation	phone
CS083	Computer Science	Mohan Das	PGT	9800011111
IP065	Informatics Prac.	Mohan Das	PGT	9800011111
MA041	Mathematics	Tashi Bhutia	TGT	9800022222

Mohan Das appears twice, and everything about him appears twice with him. If his phone number changes you must remember to fix every row. Miss one and the database now disagrees with itself — the duplication problem from the very first lesson.

2Split it into two tables

Keep teachers in one table and subjects in another. The subjects table stores only the teacher's id:

teachers — the parent table

id (PK)	name
2	Mohan Das
3	Tashi Bhutia

subjects — the child table

code	sname	id (FK)
CS083	Computer Science	2
IP065	Informatics Prac.	2
MA041	Mathematics	3

Mohan Das is now stored once. The subjects table points at him twice, using a number. Change his phone number in one place and both subjects are instantly correct, because there was only ever one copy.

3The definition

A foreign key is a column in one table that refers to the primary key of another table.

Parent table

The one being pointed at. It holds the primary key. teachers here.

Child table

The one doing the pointing. It holds the foreign key. subjects here.

Unlike a primary key, a foreign key may repeat — Mohan Das teaches two subjects, so 2 appears twice — and it may be NULL, which simply means “no teacher assigned yet”.

4The database enforces it

This is the part worth seeing rather than being told. Once a column is declared a foreign key, MySQL will not let the link be broken. Try to add a subject taught by teacher 77, who does not exist:

MySQL command line client

mysql> insert into subjects values ("MA041","Mathematics",77);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`subjects`, CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`id`) REFERENCES `teachers` (`id`))

Refused. And it works the other way too — you cannot delete a teacher while a subject still points at them:

MySQL command line client

mysql> delete from teachers where id = 2;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`subjects`, CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`id`) REFERENCES `teachers` (`id`))

This is called referential integrity

The guarantee that a foreign key always points at a row that really exists. Without it you could end up with a subject taught by teacher 77 — a reference to nobody, which no report could ever make sense of. The database refuses rather than allowing the nonsense in.

You will write this constraint later

The insert and delete commands above, and the foreign key … references … line that creates the rule, all belong to the chapters ahead. They are shown here only so you can see that the rule is real and enforced, not a convention people agree to follow.