Blog

They establish a relationship between two tables, typically a parent table and a child table. Here’s a comprehensive tutorial to get you started.

What is a FOREIGN KEY?

A FOREIGN KEY is a field or a combination of fields in a table that uniquely identifies a row in another table. It establishes a link between two tables by referencing the primary key or a unique key in another table.

Syntax:

CREATE TABLE child_table (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column)
);

Example Scenario:

Let’s consider a scenario where we have two tables: orders and customers. Each order in the orders table is associated with a customer from the customers table. We’ll use FOREIGN KEY to establish this relationship.

Step-by-Step Instructions:

1. Create the customers Table:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

2. Create the orders Table with FOREIGN KEY:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this step:

• We created the orders table with columns for order_id, customer_id, order_date, and total_amount.
• customer_id in the orders table is declared as a FOREIGN KEY referencing the customer_id column in the customers table.

3. Insert Data into the customers Table:

INSERT INTO customers (customer_id, name, email) VALUES
(1, 'John Doe', 'john@example.com'),
(2, 'Jane Smith', 'jane@example.com');

4. Insert Data into the orders Table:

INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1, '2024-05-14', 50.00),
(2, 2, '2024-05-15', 100.00);

5. Query Data:

Now, you can query data from the orders table along with related data from the customers table using JOINs:

SELECT o.order_id, o.order_date, o.total_amount, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

This query fetches order_id, order_date, total_amount, and the corresponding customer_name for each order.

Output

| order_id | order_date | total_amount | customer_name |
|----------|------------|--------------|---------------|
| 1        | 2024-05-14 | 50.00        | John Doe      |
| 2        | 2024-05-15 | 100.00       | Jane Smith    |

Summary

• We created two tables: customers and orders.
• We established a relationship between them using FOREIGN KEY.
• FOREIGN KEY ensures referential integrity, meaning you cannot insert an order for a non-existent customer.
• JOINs allow us to retrieve related data from multiple tables.

That’s the basics of using FOREIGN KEYs in SQL. Practice and explore more to master this concept!