#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

Primary Key vs Foreign Key in MySQL: Key Differences

Take a training institute's database. Each course has a course_id, and no two courses share one: that is a primary key, and it answers "which row is this?". Each batch also stores a course_id saying which course it belongs to: that is a foreign key, and it answers "which row in the other table does this point to?". The primary key protects identity inside one table. The foreign key protects the links between tables.

Differences in one table

Primary keyForeign key
PurposeUniquely identifies each rowLinks a row to a row in another (or the same) table
Per tableExactly one (it may span several columns)Any number
DuplicatesNot allowedAllowed: many batches can point to one course
NULLNot allowedAllowed unless you add NOT NULL; NULL means "no link"
IndexIs the clustered index in InnoDBMySQL needs an index on it and creates one if missing
Error when broken1062 duplicate, 1048 NULL1452 on the child side, 1451 on the parent side
EngineAnyEnforced by InnoDB only

Three linked tables

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  title     VARCHAR(30) NOT NULL
);

CREATE TABLE batches (
  batch_id   INT PRIMARY KEY,
  course_id  INT NOT NULL,
  start_date DATE,
  CONSTRAINT fk_batch_course FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE enrolments (
  batch_id      INT,
  student_email VARCHAR(40),
  PRIMARY KEY (batch_id, student_email),          -- composite primary key
  CONSTRAINT fk_enrol_batch FOREIGN KEY (batch_id)
    REFERENCES batches(batch_id) ON DELETE CASCADE
);

INSERT INTO courses VALUES (1,'SQL for Analysts'), (2,'Python Basics');
INSERT INTO batches VALUES (11,1,'2026-12-01'), (12,1,'2027-01-05'), (21,2,'2026-12-08');
INSERT INTO enrolments VALUES
(11,'priya@example.com'), (11,'sahil@example.com'), (12,'priya@example.com'), (21,'ankit@example.com');

batch_id is the primary key of batches and also a foreign key inside enrolments. The same column name plays a different role in each table. In enrolments the primary key spans two columns: Priya can join many batches, but she cannot join the same batch twice.

What the primary key refuses

INSERT INTO courses VALUES (1, 'Excel');
INSERT INTO courses VALUES (NULL, 'Excel');
INSERT INTO enrolments VALUES (11, 'priya@example.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'courses.PRIMARY'
ERROR 1048 (23000): Column 'course_id' cannot be null
ERROR 1062 (23000): Duplicate entry '11-priya@example.com' for key 'enrolments.PRIMARY'

What the foreign key refuses

From the child side, you cannot point at a course that does not exist:

INSERT INTO batches VALUES (31, 3, '2026-12-15');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`pk`.`batches`, CONSTRAINT `fk_batch_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ...)

From the parent side, you cannot delete a course that batches still use, because this key says ON DELETE RESTRICT:

DELETE FROM courses WHERE course_id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ...

Referential actions: what happens to the children

The ON UPDATE and ON DELETE clauses decide what MySQL does to child rows when the parent changes.

ON UPDATE CASCADE

UPDATE courses SET course_id = 100 WHERE course_id = 1;
SELECT batch_id, course_id FROM batches ORDER BY batch_id;
batch_idcourse_id
11100
12100
212

Both SQL batches followed the course to its new ID automatically. In practice, primary keys should rarely change. A meaningless surrogate key (an auto-increment number) never needs to.

ON DELETE CASCADE

DELETE FROM batches WHERE batch_id = 11;
SELECT batch_id, student_email FROM enrolments ORDER BY batch_id, student_email;
batch_idstudent_email
12priya@example.com
21ankit@example.com

Deleting batch 11 silently removed its two enrolments. That suits rows that have no meaning without their parent. It is dangerous for anything you may need later, such as invoices or attendance, where RESTRICT is the safer choice.

ActionEffect on child rows
RESTRICT / NO ACTIONRefuse the parent change (the default; the two behave the same in InnoDB)
CASCADEDelete or update the children too
SET NULLSet the foreign key column to NULL (the column must allow NULL)

Check what each key does with:

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'pk'
ORDER BY CONSTRAINT_NAME;
CONSTRAINT_NAMETABLE_NAMEREFERENCED_TABLE_NAMEUPDATE_RULEDELETE_RULE
fk_batch_coursebatchescoursesCASCADERESTRICT
fk_enrol_batchenrolmentsbatchesNO ACTIONCASCADE

Rules that trip people up

SELECT b.batch_id, b.course_id
FROM batches b
LEFT JOIN courses c ON c.course_id = b.course_id
WHERE c.course_id IS NULL;
batch_idcourse_id
9942

Batch 99 was inserted with checks switched off and points to a course that does not exist.

Choosing a primary key

All six constraint types are covered with examples in MySQL constraints explained. For deciding which tables need keys in the first place, see normalization. To query across these links, see MySQL joins explained. For what the primary key means for storage, see clustered vs non-clustered indexes.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →