Define foreign key. What is this concept used for?

In the realm of databases, integrity refers to the accuracy and consistency of data stored in a database. To ensure database integrity, a set of rules called integrity constraints is implemented, governing the permissible operations that can be performed on the data.

These constraints are crucial as they help prevent data errors and inconsistencies which could corrupt the database or lead to inaccurate information being provided to users or applications. Database integrity includes several types, such as domain integrity, ensuring data falls within a specific range or set of values, and referential integrity, which involves maintaining the correctness of relationships between tables via foreign keys.

One of the pillars that uphold database integrity is referential integrity, which ensures relationships between tables remain consistent. When a database has referential integrity, it means that any foreign key field must only contain values that exist as primary keys in the related table, or be null if the relationship is optional.

This rule prevents the creation of orphan records—records that refer to non-existent data in another table. For example, deleting a record that has related data in another table would violate referential integrity if not handled correctly. The database management system enforces referential integrity by disallowing the deletion or alteration of related data, maintaining the logical link between tables.

At the heart of a relational database's table lies the primary key. It's a unique identifier for each record in a table, such as a customer ID or an invoice number.

A primary key must contain unique values and it cannot contain null values. This exclusivity ensures that each record can be retrieved, updated, or deleted without ambiguity. Often, the primary key is used to reference other records in different tables within the database, thereby establishing a direct link known as a foreign key in the referencing table.

The structure of a database is a bit like a collection of spreadsheets, where database tables equate to individual sheets. Each table consists of rows and columns, with rows representing records and columns representing attributes.

Tables are designed to hold information about one type of entity only, for example, there might be a table for students, one for courses, and another for enrollment details. Database tables can be related to each other using primary and foreign keys, allowing them to maintain a network of interconnected data points that can be efficiently organized and queried.