Learning SQL: PRIMARY VS FOREGIN KEYS

What Are PRIMARY and FOREIGN Keys?

In SQL, PRIMARY KEY and FOREIGN KEY are essential concepts for defining relationships between tables and ensuring data integrity.

• A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row in a table.
• A FOREIGN KEY is a column that creates a link between two tables by referencing the PRIMARY KEY of another table.

Think of PRIMARY KEY as a unique identifier (like a Social Security Number), while a FOREIGN KEY establishes a relationship (like a customer ID in an orders table that links back to the customers table).

How Do PRIMARY and FOREIGN Keys Work?

PRIMARY KEY: Ensuring Uniqueness

A PRIMARY KEY ensures that:

• Each row has a unique identifier.
• The column(s) cannot contain NULL values.
• A table can have only one PRIMARY KEY.

Basic Syntax for PRIMARY KEY:

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

Here, customer_id is the PRIMARY KEY, meaning no two customers can have the same ID.

FOREIGN KEY: Linking Tables

A FOREIGN KEY ensures:

• A relationship between two tables (parent and child).
• The value in the FOREIGN KEY column must exist in the referenced PRIMARY KEY column (or be NULL, if allowed).

Basic Syntax for FOREIGN KEY:

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

Here, customer_id in the orders table references customer_id in the customers table, ensuring every order is linked to a valid customer.

Example: How PRIMARY and FOREIGN Keys Work Together

Let’s say we have two tables:

1. customers Table (Parent Table)

   customer_id (PK)	customer_name	email
   1	John Doe	john@example.com
   2	Jane Smith	jane@example.com

2. orders Table (Child Table)

   order_id (PK)	customer_id (FK)	order_date	amount
   101	1	2023-01-15	150.00
   102	2	2023-01-16	200.00

What Happens Here?

• The customer_id in orders must match an existing customer_id in the customers table.
• If you try to insert an order with customer_id = 3 (which doesn’t exist in customers), SQL will reject it to maintain referential integrity.

Key Points to Remember

✅ PRIMARY KEY

• Must be unique and non-NULL.
• Only one per table.
• Can be a single column or a combination (composite key).

✅ FOREIGN KEY

• Ensures data consistency by linking to a PRIMARY KEY.
• Can reference a table in the same or a different database.
• Prevents orphaned records (e.g., an order without a valid customer).

✅ Common Use Cases

• One-to-Many Relationships: One customer can have many orders.
• Many-to-Many Relationships: Requires a junction table with FOREIGN KEYS.

Recap

By understanding PRIMARY and FOREIGN KEYS, you can design efficient, well-structured databases that maintain data integrity.

Resources

PostgreSQL Primary Key

PostgreSQL Foreign Key

Thank you

Thank you for your time and for reading this!