Foreign keys in SQL are a fundamental concept used to establish relationships between tables in a relational database. A foreign key is a column or a group of columns in one table that refers to the primary key in another table. This relationship enforces data integrity by ensuring that each value of the foreign key corresponds to a valid entry in the referenced primary key table. For example, consider a database with two tables: Customers and Orders. The Customers table might have a primary key named CustomerID, and the Orders table can include a foreign key named CustomerID that points back to the Customers table. This setup ensures that every order is linked to a valid customer.
When you define a foreign key in SQL, you typically specify it when creating or altering a table. This is done using the FOREIGN KEY constraint in the table definition. For instance, you might write the creation of the Orders table as follows:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
This statement creates the Orders table with a foreign key that connects CustomerID in the Orders table to the primary key CustomerID in the Customers table. This relationship ensures referential integrity, meaning you cannot insert an order with an invalid CustomerID — that is, one that doesn't exist in the Customers table.
Moreover, foreign keys can also have additional rules associated with them, such as cascading updates and deletes. If a customer is deleted from the Customers table, you might want all their related orders removed as well. You can define this behavior using ON DELETE CASCADE, which will automatically delete related records in the Orders table when a corresponding CustomerID is removed from the Customers table. This feature helps maintain coherent data across related tables and minimizes the risks of orphaned records. Overall, foreign keys play a crucial role in structuring relational databases and ensuring that data remains consistent and reliable.