🔐 Understanding PRIMARY KEY, FOREIGN KEY, and CASCADE in SQL

Relational databases rely on keys to maintain data integrity. In this beginner-friendly tutorial, you'll explore what PRIMARY KEY and FOREIGN KEY are, how they work together, and how ON DELETE CASCADE and ON UPDATE CASCADE ensure automatic consistency in your database.

🗝️ What is a PRIMARY KEY?

A PRIMARY KEY uniquely identifies each row in a table.

It must be unique.
It cannot contain NULL values.
A table can have only one PRIMARY KEY.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

Here, student_id is the PRIMARY KEY.

🔗 What is a FOREIGN KEY?

A FOREIGN KEY creates a relationship between two tables, enforcing referential integrity.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_name VARCHAR(100),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

student_id in enrollments must exist in the students table.

✅ Insert Data Example

-- Insert students
INSERT INTO students VALUES (1, 'Alice', 20), (2, 'Bob', 22);

-- Insert enrollments
INSERT INTO enrollments VALUES (101, 1, 'Math'), (102, 2, 'Physics');

❌ Invalid Example: This will fail because student_id = 10 doesn’t exist in students.

INSERT INTO enrollments VALUES (103, 10, 'Biology');

🔄 What is ON DELETE CASCADE?

Use ON DELETE CASCADE to automatically delete related rows in the child table when the parent row is deleted.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_name VARCHAR(100),
  FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
);

If you run:

DELETE FROM students WHERE student_id = 1;

→ All enrollments with student_id = 1 are automatically deleted.

Tip: This helps keep your database clean by preventing orphaned records.

🔁 What is ON UPDATE CASCADE?

ON UPDATE CASCADE ensures that if the parent table's primary key is updated, all corresponding foreign keys in child tables are updated automatically.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_name VARCHAR(100),
  FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON UPDATE CASCADE
);

UPDATE students SET student_id = 10 WHERE student_id = 1;

→ This will also update all related student_id values in enrollments from 1 to 10.

Note: Oracle does not support ON UPDATE CASCADE directly. Use MySQL or triggers for this behavior in Oracle.

🧠 Practice Exercise

Try this:

-- Delete Bob
DELETE FROM students WHERE student_id = 2;

-- Check enrollments
SELECT * FROM enrollments;

✅ If you used ON DELETE CASCADE, all enrollments of Bob will also be removed automatically.

📋 Summary Table

Concept	Purpose	Unique?	Allows NULL?
PRIMARY KEY	Identifies records uniquely	✅ Yes	❌ No
FOREIGN KEY	Links tables together	❌ Not required	✅ Yes
ON DELETE CASCADE	Deletes child rows automatically	➖	➖
ON UPDATE CASCADE	Updates child keys automatically	➖	➖

🎯 Final Thoughts

PRIMARY and FOREIGN KEYS build the foundation of relational databases. By using ON DELETE and ON UPDATE CASCADE, you can simplify data management and ensure referential integrity. These features are powerful — use them wisely depending on your database system!

Trust Coding : Programming

🗝️ PRIMARY KEY vs FOREIGN KEY in SQL with CASCADE Explained