What is a foreign key in SQL?

Foreign key is a column in a table that references a primary key in another table. The most important thing to understand is that a foreign key is not just an ID value pointing to a specific record in another table - it’s a mechanism that enforces.

Simple example

We have users and orders tables. A user can create orders, so a record in the orders table must be linked to a specific record in users. To connect them, we use the orders.user_id field, which indicates who created the order.

From a practical standpoint, we could implement this relationship without a foreign key by simply storing the ID as a number in the user_id column. But this approach has several significant downsides:

1. The database doesn’t enforce the relationship between the tables. For example, in this case we have a one-to-many relationship (one user has many orders). But if we try to query orders for a specific user, the database will scan the entire table, because it doesn’t know which orders belong to that user - user_id is just a number, not a reference to another table.
2. There’s no protection against non-existent data. If we try to write the value 100 into user_id, we won’t get any error telling us that such a user doesn’t exist.
3. There’s no control over what happens when a user is deleted or updated. If, when deleting a user, we also need to delete all of their orders, then without a foreign key, we’ll have to handle it in business logic. A foreign key, on the other hand, provides a referential actions mechanism that lets you control this process. You can read more about this mechanism here - https://devs-hive.tech/interview-qa/referential-actions-sql.

All these problems are solved by a foreign key, which enforces a strong relationship between tables and allows us to define relationships between them.