Skip to content

Click to use (opens in a new tab)

What is a Foreign Key

Introduction to Foreign Keys

A Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a link between two tables, ensuring referential integrity by enforcing rules that control how related data is added, updated, or deleted. Foreign keys are fundamental in relational database design for maintaining relationships between entities.

Key Characteristics

  • Referential Integrity: Ensures that the relationship between tables remains consistent.
  • Constraints: Defines rules for actions such as insert, update, and delete operations on related rows.
  • Normalization: Helps achieve normalized database design by reducing redundancy and improving data integrity.

Example: Creating Tables with Foreign Keys

SQL Example

Create Customers Table

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

Create Orders Table with Foreign Key

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

In this example:

  • The Orders table has a foreign key customer_id that references the customer_id in the Customers table.
  • This ensures that each order must be associated with an existing customer.

Referential Actions

Foreign key constraints can specify what should happen when a referenced row is updated or deleted:

  • CASCADE: Automatically updates or deletes the corresponding rows in the child table.
  • SET NULL: Sets the foreign key column to NULL.
  • RESTRICT: Prevents the operation if there are dependent rows.
  • NO ACTION: Similar to RESTRICT but checks the constraint after trying to perform the operation.

Example: CASCADE on Delete

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE
);

With ON DELETE CASCADE, deleting a customer will automatically delete all their orders.

Example: SET NULL on Update

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON UPDATE SET NULL
);

Here, updating a customer's ID will set the customer_id in the Orders table to NULL.

Benefits of Using Foreign Keys

  • Data Integrity: Ensures that related data remains consistent across tables.
  • Relationship Management: Simplifies managing complex relationships between entities.
  • Automation: Automates certain actions like cascading updates and deletions.
  • Validation: Provides built-in validation for insert and update operations.

Conclusion

Foreign keys are essential for establishing and maintaining relationships between tables in a relational database. By enforcing referential integrity, they ensure that data remains accurate and consistent, facilitating robust and reliable database applications.

Chat2DB - AI Text2SQL Tool for Easy Database Management

Click to use (opens in a new tab)

What can Chat2DB do?