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?
It's not strictly needed for the query, it's true. It exists for several reasons:
- As a constraint on the table to stop you inserting something that doesn't point to anything;
- As a clue for the optimizer; and
- 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).