Foreign Key — SQL Technical Interview Questions & Answers

Foreign Key

Foreign Key

  • FOREIGN KEY says this column refers to a PRIMARY KEY (or UNIQUE) in another table. marks.roll REFERENCES students(roll). You cannot insert marks for roll 99 if there is no student 99. You cannot delete student 12 if marks still point to 12, unless ON DELETE CASCADE.
  • Needs InnoDB. Both columns same type. CREATE TABLE child (…, FOREIGN KEY (roll) REFERENCES students(roll));
  • ON DELETE CASCADE: delete parent, child rows go too. ON DELETE SET NULL: child roll becomes NULL. Default RESTRICT/NO ACTION: reject the delete.
  • Trap: MyISAM silently ignoring FK. Trap: CASCADE on a table you did not mean to empty.
  • On the example next to this theory: Foreign Key — keys and constraints protect data integrity; indexes speed lookups.

Technical Interview Tip: marks.roll → students.roll. Cannot orphan a marks row.

Code Examples for Foreign Key

Foreign Key — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

Foreign Key — keys and constraints protect data integrity; indexes speed lookups.

How to Explain Foreign Key to an Interviewer

What does a FOREIGN KEY do?

Child value must exist in parent Stops orphan rows

ON DELETE CASCADE?

Deleting the parent deletes children