Primary Key and Foreign Key in MySQL

In this lesson: Create primary and foreign keys in MySQL with CREATE TABLE and ALTER, rules and errors explained with output.

Why keys exist

A database is rarely one big table. Real information is split across several tables — students, courses, teachers, orders — and those tables need to connect to each other reliably. Keys are how that connection is made safe. Without keys, a database is just loose spreadsheets; with keys, it becomes a linked, trustworthy system.

There are two keys you must understand first. A primary key answers "which exact row is this?" inside one table. A foreign key answers "which row in another table does this belong to?" Together they turn separate tables into a connected whole where relationships cannot break.

Primary key: the row identity

A primary key is a column whose value is different for every row and is never empty. It is the reliable handle you use to find, update or delete one specific row.

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO customers VALUES (1, 'Aarav'), (2, 'Diya');
INSERT INTO customers VALUES (1, 'Kabir');  -- fails, id 1 exists

MySQL rejected the third row because id 1 is already used. That guarantee — every id is unique and present — is exactly what lets other tables safely point to a customer by id.

Foreign key: linking tables

A foreign key is a column in one table that must match a primary key value in another table. It creates and protects the link between them.

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(8,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Here customer_id in orders is a foreign key. It must hold an id that already exists in customers. This single line stops a whole category of bugs — you can never create an order for a customer who does not exist.

Seeing both keys work together

Let us insert valid and invalid data to watch the relationship being enforced.

INSERT INTO orders VALUES (101, 1, 500.00);   -- ok, customer 1 exists
INSERT INTO orders VALUES (102, 9, 300.00);   -- fails, customer 9 missing

The first order linked cleanly to an existing customer. The second was blocked because customer 9 is not in customers. The primary key made customer 1 findable; the foreign key made the link trustworthy.

ON DELETE and ON UPDATE

What should happen to orders when a customer is deleted? You decide with ON DELETE rules.

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
);

With ON DELETE CASCADE, deleting customer 1 automatically deletes all of customer 1's orders. Powerful, but use it consciously — the deletion spreads to child tables.

Primary key vs foreign key

The clearest way to hold the difference: a primary key stands for the row's own identity, while a foreign key is a reference to some other row's identity.

Common mistakes

• Mismatched data types. The foreign key and the primary key it points to must be the same type, or MySQL refuses the link.
• Inserting the child before the parent. The parent row must exist first, otherwise the foreign key check fails.
• Forgetting the parent's column must be a key. A foreign key can only reference a primary key or a unique column, not any random column.
• Using CASCADE without thinking. It can delete far more than you intended if relationships chain together.

Summary

• A primary key uniquely identifies each row and is never NULL — one per table.
• A foreign key links to a primary key in another table — many allowed per table.
• Foreign keys stop orphan rows and keep relationships valid automatically.
• ON DELETE CASCADE / SET NULL / RESTRICT control what happens to child rows.
• The two columns must share a compatible data type.

Next, protect these columns with rules in constraints in MySQL, or organize your tables cleanly with normalization in DBMS.