What is the purpose of a foreign key in a relational database?

A foreign key is a column or set of columns in one table that refers to the primary key of another table. The purpose of a foreign key is to enforce referential integrity between the two tables. This means that a value in the foreign key column(s) of one table must match a value in the primary key column(s) of another table, or the transaction will fail.

For example, consider a relational database with two tables: "Customers" and "Orders". The "Customers" table has a primary key of "CustomerID", and the "Orders" table has a foreign key of "CustomerID" that refers to the "CustomerID" column in the "Customers" table. This means that when inserting a new order into the "Orders" table, the value of the "CustomerID" column in the new order must exist in the "CustomerID" column of the "Customers" table, or the transaction will fail.

Foreign keys help maintain the integrity and consistency of data in a database. They prevent the insertion of invalid data by ensuring that each foreign key value has a corresponding primary key value in another table. In addition, foreign keys can be used to JOIN tables, allowing for more complex queries and data analysis.

Here’s an example of how foreign keys are used in SQL code:

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    Name varchar(255)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int,
    OrderDate date,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, the "Orders" table has a foreign key constraint on the "CustomerID" column, which references the "CustomerID" column in the "Customers" table. This ensures that every order in the "Orders" table is associated with a valid customer in the "Customers" table.