# How do foreign keys work in SQL?

Source: https://zilliz.com/ai-faq/how-do-foreign-keys-work-in-sql
Author: zilliz

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:

```sql
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.