Previous
Written by: rJTutorial Team | Last Updated:

SQL FOREIGN KEY

In relational databases, data is rarely stored in a single, massive table. Instead, it is organized into smaller, logical tables. A FOREIGN KEY is the "glue" that holds these tables together. It is a constraint that links a column (or a group of columns) in one table to the PRIMARY KEY of another table.

This relationship establishes a parent-child hierarchy. The table containing the primary key is called the Parent (or Referenced) table, and the table containing the foreign key is the Child (or Referencing) table. By enforcing this link, the database ensures referential integrity, meaning you cannot have a child record that points to a non-existent parent.

Developer Tip: Think of a Foreign Key as a pointer. In an e-commerce app, an Orders table "points" to a Users table to identify which customer placed the order.

 

Key Features of SQL FOREIGN KEY

  • Data Consistency: It ensures that values in the child table must already exist in the parent table. You can't assign an order to a Customer ID that doesn't exist.
  • Prevents Orphaned Records: It stops developers from accidentally deleting parent records that are still being referenced by other tables.
  • Relationship Mapping: It defines how data entities interact, such as One-to-Many or Many-to-Many relationships.
  • Multi-Column Support: A foreign key can reference a composite primary key (a key made of multiple columns) in the parent table.
Common Mistake: Forgetting that the data types must match. A Foreign Key column must have the exact same data type (e.g., INT, UUID, or VARCHAR) as the Primary Key it references.

 

SQL FOREIGN KEY Syntax

Creating a FOREIGN KEY in Table Creation

When designing a new schema, you define the foreign key relationship directly within the CREATE TABLE statement. This ensures the rule is active from the moment the table is born.

-- The Parent Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL
);

-- The Child Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    EmployeeID INT, -- This will store the link
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
  • In this example, EmployeeID in the Orders table is the foreign key.
  • It "references" the EmployeeID in the Employees table.
  • The database will reject any attempt to insert an order with an EmployeeID that is not present in the Employees table.
Best Practice: Always give your constraints meaningful names. Instead of letting the database generate a random name, use a format like FK_ChildTable_ParentTable.

 

Adding a FOREIGN KEY to an Existing Table

In real-world projects, requirements change. You might need to link two existing tables that were previously independent. We use the ALTER TABLE command for this.

ALTER TABLE Orders  
ADD CONSTRAINT FK_Employee_Order  
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);
Watch Out: If you try to add a Foreign Key to a table that already contains data, the operation will fail if any "invalid" rows exist (rows where the ID doesn't exist in the parent table). You must clean up your data first!

 

Removing a FOREIGN KEY

There are times—such as during a major database refactor or when migrating data—that you might need to drop a constraint.

-- Syntax for SQL Server / Oracle / MySQL
ALTER TABLE Orders  
DROP CONSTRAINT FK_Employee_Order;

-- Syntax for MySQL (specifically)
ALTER TABLE Orders
DROP FOREIGN KEY FK_Employee_Order;

 

ON DELETE and ON UPDATE Actions

What happens to the child record when the parent record is deleted? SQL allows you to define "Referential Actions" to handle these scenarios automatically.

1. Cascade (The "Trickle Down" effect)

If a parent record is deleted, all associated child records are automatically deleted. This is useful for "all-or-nothing" data, like an Order and its individual OrderItems.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)  
    ON DELETE CASCADE
);
  • If Employee #10 is fired and deleted from Employees, every order they ever handled in Orders will be permanently wiped out. Use this with caution!

2. Set NULL (The "Detach" effect)

If the parent is deleted, the child record remains, but the reference column is set to NULL. This is common when you want to keep historical records but the association no longer exists.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)  
    ON DELETE SET NULL
);
  • If an employee is deleted, the EmployeeID field in Orders becomes NULL, effectively making the order "unassigned" rather than deleting it.
Developer Tip: There is also NO ACTION (the default). If you try to delete a parent that has children, the database will simply throw an error and stop you. This is the safest way to prevent accidental data loss.

 

Summary

  • A FOREIGN KEY creates a logical link between two tables, ensuring valid relationships.
  • It protects referential integrity, preventing "orphaned" data that points to nothing.
  • You can manage constraints during table creation or later using ALTER TABLE.
  • Use ON DELETE CASCADE to sync deletions, or SET NULL to preserve child data without a parent.
  • Properly indexed Foreign Keys can also significantly improve the performance of JOIN queries.
Previous