Primary Key vs Foreign Key
In this lesson: Difference between primary key and foreign key with comparison table, MySQL examples and output. Read in 3 min. In Hindi & English.
Two keys that work together
The primary key and foreign key are the two most important keys in a relational database. A primary key identifies each row in a table; a foreign key connects one table to another. Together they let you split data into clean tables and still link everything correctly.
Primary key
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
The primary key (id) uniquely identifies each customer. It cannot be null, cannot repeat, and there is only one per table. Every table should have a primary key.
Foreign key
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Here customer_id is a foreign key that points to customers.id. It links each order to a customer, so you do not repeat the customer's name and details in every order.
How they link tables
The foreign key stores just the id of the related row. To see full details, you join the tables:
SELECT orders.id, customers.name, orders.amount FROM orders JOIN customers ON orders.customer_id = customers.id;
This connects each order back to its customer through the key — no duplicated data, always consistent.
Referential integrity
A foreign key enforces referential integrity: you cannot insert an order with a customer_id that does not exist in customers. MySQL rejects it. This stops "orphan" rows and keeps the data trustworthy. A foreign key can be null (optional link), but a primary key never can.
Side-by-side
| Point | Primary key | Foreign key |
|---|---|---|
| Purpose | Identify each row | Link to another table |
| Null allowed | No | Yes (optional link) |
| How many per table | One | Many |
| Duplicates | Not allowed | Allowed |
| Points to | Nothing (it is the id) | Another table's key |
Common mistakes
- No primary key. Every table needs one to identify rows.
- Mismatched types. The foreign key column must match the referenced key's type.
- Referencing a non-unique column. A foreign key must point to a primary or unique key.
- Deleting referenced rows carelessly. Removing a customer with orders can break integrity unless handled.
Summary
- A primary key uniquely identifies each row; never null, one per table.
- A foreign key links a table to another table's key.
- Foreign keys can be null and a table can have many.
- They enforce referential integrity, preventing orphan rows.
- Together they let you split data cleanly and join it back.
Next, see all key types in keys in DBMS explained, or enforce rules with referential integrity.
