SQL · Theory

Foreign Key

Theory

73/439

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.

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

marks.roll → students.roll. Cannot orphan a marks row.

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.

Short notes

DefFK = this value must exist in the parent table.
RuleInnoDB. Same types. ON DELETE: RESTRICT / CASCADE / SET NULL.
TrapMyISAM. Accidental CASCADE.

SQL · Theory

Foreign Key

← All stacks

Theory

73/439

Foreign Key

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.

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

marks.roll → students.roll. Cannot orphan a marks row.

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.

Short notes

DefFK = this value must exist in the parent table.
RuleInnoDB. Same types. ON DELETE: RESTRICT / CASCADE / SET NULL.
TrapMyISAM. Accidental CASCADE.

Foreign Key

Foreign Key — sample query

Short notes

Questions

Foreign Key

Foreign Key — sample query

Short notes

Questions