Why do I need to use foreign key if I can use WHERE?

Question

A beginners' question about foreign key in MySQL.

In w3school it says,

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

And also there is WHERE,

WHERE id = page_id 

So if I can use WHERE for linking the tables, what is the main purpose of having foreign key?

Answer 1

It's not strictly needed for the query, it's true. It exists for several reasons:

1. As a constraint on the table to stop you inserting something that doesn't point to anything;
2. As a clue for the optimizer; and
3. For historical reasons where is was more needed.

(1) is probably the important one of the three. This is called referential integrity. It means that if there is a value in a foreign key there will be a corresponding record with that value as a primary key in the parent table.

That being said, not all databases support referential integrity (eg MySQL/MyISAM tables) and those that do don't necessarily enforce it (for performance reasons).

Answer 2

The Foreign is used for referential integrity.

See An introduction to foreign keys and referential integrity in MySQL