Foreign Keys and SQL relationships

Foreign Keys at a glance

When you store references to related rows in your database, you somehow have to ensure that a referential integrity is given. This means that you have to make sure that a referenced rows actually exists. Especially when you’re storing critical data, a foreign key is a good way to enforce this referential integrity.

If foreign keys are supported by your database engine, you don’t have add extra logic to make sure every referenced row exists.Generally speaking, a foreign key is a special index on the column which stores the reference. If you’re able at all to use foreign keys depends on the database system and engine you’re using. In MySQL for example, the only database type that support foreign keys is InnoDB.

However, it’s not always necessary to rely on the database to enforce perfect integrity. It can have an impact on performance, especially with bigger databases. Therefor you have to make this design decision for every database. As a rule of thumb: If your data is important and mission critical, like financial transactions or the like, it’s better to go with the foreign key. If you store logs or other non-critical data, you probably don’t need a foreign key.