What Is a Foreign Key in SQL?

#database #sql #foreignkey #datastructures

A foreign key is a validation rule in the database that links one table to another by matching values.
It helps keep your data linked correctly and stops you from adding values that don’t exist.

Imagine you have two tables:

One called Orders, where you track purchases - like a unique order ID, date, and total.
Another called Customers, where each customer has a unique ID, along with their name and contact details.

To know which customer placed each order, you add a customer_id column to the Orders table.

But just adding the column isn’t enough. You need to connect it to the Customers table.

You do this by creating a foreign key - a link between Orders.customer_id and Customers.customer_id (the primary key).

The foreign key ensures that the values in Orders.customer_id must exist in Customers.customer_id.

The database won’t allow you to insert an order with a customer ID that doesn’t exist in the Customers table.

If you try, it will return an error.

This helps keep your data clean and consistent.

Real Data Example: How a Foreign Key Keeps Orders Linked to Customers

Customer ID = 52 (Tiffany Ritter) placed Order #33 on 10.01.2000.
Thanks to the foreign key, we can link this order back to the correct customer to see what she bought.

Now, imagine we try to insert an order with a customer_id of 1000 into the Orders table, but there’s no customer with ID 1000 in the Customers table.

Because the foreign key is in place, the database checks if that customer exists first.

Since it doesn’t, the database blocks the insert and shows an error like the one below.

This is called referential integrity - it means the database won’t let you create links to data that doesn’t exist.

Syntax: Foreign Key in SQL

Let’s consider we have two tables: Customers and Orders.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderDate DATE,
  CustomerID INT,
 );

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(100)
  Email VARCHAR(100)
  Country VARCHAR(50)
);

Now, let's add a foreign key between these tables:

ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

In this case, Orders.CustomerID is the foreign key.
It must match a value from Customers.CustomerID - the primary key.

Creating Foreign Keys Visually in DbSchema

In DbSchema, you don’t need to write SQL by hand. You can drag a line from one table to another and define the relationship visually.

How It Works:

Open your project or reverse-engineer the database.
Drag the referring column of the primary key column from the child column (e.g. Orders.CustomerID) to the parent column (e.g. Customers.CustomerID).
DbSchema will create a foreign key for you.

It’s simple, visual, and you immediately see how tables connect.

What If the Foreign Key Doesn’t Exist?

In some databases (especially NoSQL or legacy systems), foreign keys might not exist.

DbSchema lets you create virtual foreign keys - relationships that exist only in the diagram, not in the actual database.

This is useful for:

Documentation
Understanding data relationships
Building queries visually without needing actual constraints
Viewing data from related tables easily

Conclusion

Foreign keys do more than just link tables; they protect your data.

They make sure that every reference in your database points to something real. No missing customers, no orphaned orders, no broken relationships.

When you use foreign keys:

Your data becomes more organized and reliable.
Your SQL queries become easier to write and understand.
Your database design is clearer for you and your team.

And with free tools like DbSchema, you don’t need to write complex SQL to define them - you can create and even simulate foreign keys visually, making design and documentation faster than ever.

If you want to learn more about foreign keys, check out these helpful resources:

These explain the concept with more examples, syntax differences, and best practices.