What is the function of a foreign key?

A foreign key in a database is used to link two tables together, ensuring data integrity and consistency.

In more detail, a foreign key is a column or a set of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them. This is a fundamental aspect of database relationships, particularly in enforcing referential integrity, a key aspect of the relational database model.

The primary function of a foreign key is to ensure data integrity and consistency. This is achieved by maintaining the relationships between tables, preventing actions that would destroy these links. For instance, a foreign key constraint prevents actions that would leave rows in a child table without a corresponding row in the parent table.

Foreign keys also play a crucial role in database operations such as joins and queries. They enable the database to match records in one table with records in another, based on the foreign key. This is particularly useful in complex databases where data is spread across multiple tables.

For example, consider a database for a school. There might be a table for 'Students' and another for 'Courses'. Each student can enrol in multiple courses, and each course can have multiple students. In the 'Students' table, there might be a primary key 'student_id', and in the 'Courses' table, a primary key 'course_id'. To track which student is enrolled in which course, a third table 'Enrolments' might be created, with foreign keys 'student_id' and 'course_id' linking back to the respective primary keys in the 'Students' and 'Courses' tables.

In summary, a foreign key is a vital tool in relational databases, ensuring data integrity, enforcing relationships between tables, and facilitating complex database operations.