What are foreign keys?

Foreign keys are columns in a database table that are used to establish a relationship with another table's primary key. They are used to enforce referential integrity between the two tables, ensuring that data in one table is consistent with data in another table. Foreign keys help maintain the integrity of the data by preventing actions that would result in orphaned records or inconsistent data.

Foreign Keys

Definition:

Foreign keys are database constraints that ensure the integrity of relationships between multiple tables. They establish a connection between a column (typically a primary key) in one table (the parent table) and a column (typically a foreign key) in another table (the child table).

Purpose:

- Data Integrity: Foreign keys ensure that data in the child table is always valid and consistent with data in the parent table.
- Referential Integrity: They prevent orphaned records in the child table by prohibiting the deletion of parent records without deleting the corresponding child records.
- Data Retrieval: Foreign keys facilitate efficient data retrieval by allowing joins between tables based on matching key values.

Example:

Consider the following database tables:

```sql
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
```

In this example, the `customer_id` column in the `Orders` table is a foreign key that references the `customer_id` column in the `Customers` table (the parent table). This means that:

- Each order in the `Orders` table must have a valid customer ID that exists in the `Customers` table.
- If a customer is deleted from the `Customers` table, all orders associated with that customer will be automatically deleted from the `Orders` table.
- Joins can be performed between the `Customers` and `Orders` tables using the `customer_id` column as the common key.

Benefits of Foreign Keys:

- Ensure data accuracy and consistency
- Enforce business rules
- Improve data retrieval and analysis
- Reduce data redundancy
- Protect against data inconsistencies

Additional Considerations:

- Foreign keys can also reference non-primary key columns.
- Multiple foreign keys can be defined for a table.
- Foreign keys are not enforced by default in some database systems.