Foreign Keys

Why Foreign Keys?

Before foreign keys, merging every thing into one table caused issues:

• Unclear focus: Hard to separate employee vs. department data.
• Redundant storage: Same fields repeated across rows.
• Poor scalability: Changing one part affected the whole table.

Solution: Split into multiple tables (e.g., emp and dep) and use foreign keys to maintain relationships.

A foreign key is a field that links rows between tables, typically referencing a primary key in another table.

Table Relationships

One-to-Many

How to idenitfy: Apply the "standpoint shift" method. For example, with emp (employee) and dep (department):

• From employee side: Can one employee belong to multiple departments? → No.
• From department side: Can one department have multiple employees? → Yes.
• Conclusion: One-to-many relationship. The foreign key must be placed on the "many" side (the emp table).

SQL example:

CREATE TABLE dep (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(32),
    dep_desc VARCHAR(32)
);

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32),
    age INT,
    dep_id INT,
    FOREIGN KEY(dep_id) REFERENCES dep(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

INSERT INTO dep(dep_name, dep_desc) VALUES('Human Resources', 'Manage talent');
INSERT INTO emp(name, age, dep_id) VALUES('kevin', 20, 1);

Many-to-Many

Example: Books and authors.

• From book side: Can one book have multiple authors? → Yes.
• From author side: Can one author write multiple books? → Yes.
• Conclusion: Many-to-many relationship. Use a third junction table to hold foreign keys.

SQL example:

CREATE TABLE book (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(32),
    price DECIMAL(8,2)
);

CREATE TABLE author (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32),
    addr VARCHAR(32)
);

CREATE TABLE book2author (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    author_id INT,
    FOREIGN KEY(book_id) REFERENCES book(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    FOREIGN KEY(author_id) REFERENCES author(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

INSERT INTO book(title, price) VALUES('Historical Records', 1000);
INSERT INTO book(title, price) VALUES('Journey to the West', 2000);
INSERT INTO author(name, addr) VALUES('zhangsan', 'Beijing');
INSERT INTO author(name, addr) VALUES('lisi', 'Shanghai');
INSERT INTO book2author(book_id, author_id) VALUES(1, 1);
INSERT INTO book2author(book_id, author_id) VALUES(1, 2);
INSERT INTO book2author(book_id, author_id) VALUES(2, 1);
INSERT INTO book2author(book_id, author_id) VALUES(2, 2);

One-to-One

Example: Author and author details. The foreign key can be placed on either table, but it's recommended to put it on the table queried more frequently. Add a UNIQUE constraint to ensure one-to-one.

SQL example:

CREATE TABLE author_detail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    qq VARCHAR(32),
    email VARCHAR(32)
);

CREATE TABLE author_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32),
    gender VARCHAR(32),
    author_detail_id INT UNIQUE,
    FOREIGN KEY(author_detail_id) REFERENCES author_detail(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

Important Notes

1. Create the referenced table (without a foreign key) first.
2. Insert data in to the referenced table before the referencing table.
3. Foreign key values must exist in the referenced table.
4. To allow automatic updates/deletes in the referenced table, add ON UPDATE CASCADE and ON DELETE CASCADE.

Multi-Table Queries

Two primary approaches:

1. Subquery (Nested Query)

Use the result of one query as a condition for another. Example: Find the department name for employee 'kevin'.

SELECT dep_id FROM emp WHERE name='kevin';
-- Then use that id:
SELECT * FROM dep WHERE id = (SELECT dep_id FROM emp WHERE name='kevin');

2. Join Query

Combine multiple related tables into a single virtual table (in memory) and then query it like a single table.

Types of joins:

• INNER JOIN: Only rows with matching keys in both tables.
• LEFT JOIN: All rows from the left table; NULL for unmatched right rows.
• RIGHT JOIN: All rows from the right table; NULL for unmatched left rows.
• UNION: Combine results of two SELECT statements (removes duplicates; use UNION ALL to keep duplicates).

Example:

SELECT * FROM emp LEFT JOIN dep ON emp.dep_id = dep.id
UNION
SELECT * FROM emp RIGHT JOIN dep ON emp.dep_id = dep.id;

Sample Data for Exercises

CREATE TABLE dep (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM('male','female') NOT NULL DEFAULT 'male',
    age INT,
    dep_id INT
);

INSERT INTO dep VALUES
(200,'Technology'),
(201,'Human Resources'),
(202,'Sales'),
(203,'Operations'),
(205,'Cleaning');

INSERT INTO emp(name,sex,age,dep_id) VALUES
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

Exercise Queries

Data script is provided in the full article. Below are solutions to the multi-table query exercises.

1. Query all course names and corresponding teacher names.

SELECT teacher.tname, course.cname 
FROM teacher 
INNER JOIN course ON teacher.tid = course.teacher_id;

2. Query names and average grades of students with an average score above 80.

-- Step 1: Find student IDs with avg(num) > 80
SELECT student_id, AVG(num) AS avg_num 
FROM score 
GROUP BY student_id 
HAVING AVG(num) > 80;

-- Step 2: Join with student table
SELECT student.sname, t1.avg_num 
FROM student 
INNER JOIN ( 
    SELECT student_id, AVG(num) AS avg_num 
    FROM score 
    GROUP BY student_id 
    HAVING AVG(num) > 80 
) AS t1 ON student.sid = t1.student_id;

3. Query names of students who did not take any course taught by Teacher Li Ping.

-- Step 1: Find course IDs taught by Li Ping
SELECT course.cid FROM course 
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = 'Li Ping');

-- Step 2: Find student IDs who took those courses
SELECT DISTINCT score.student_id 
FROM score 
WHERE course_id IN ( 
    SELECT course.cid FROM course 
    WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = 'Li Ping') 
);

-- Step 3: Select students not in that set
SELECT student.sname 
FROM student 
WHERE sid NOT IN (
    SELECT DISTINCT score.student_id 
    FROM score 
    WHERE course_id IN ( 
        SELECT course.cid FROM course 
        WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = 'Li Ping') 
    )
);

4. Query names and classes of students who failed two or more courses (including two).

-- Step 1: Filter scores below 60
SELECT * FROM score WHERE num < 60;

-- Step 2: Count failures per student, keep those with >=2
SELECT student_id FROM score 
WHERE num < 60 
GROUP BY student_id 
HAVING COUNT(course_id) >= 2;

-- Step 3: Join with student and class tables
SELECT student.sname, class.caption 
FROM class 
INNER JOIN student ON class.cid = student.class_id 
WHERE student.sid IN ( 
    SELECT student_id FROM score 
    WHERE num < 60 
    GROUP BY student_id 
    HAVING COUNT(course_id) >= 2 
);

Using Navicat

Navicat is a GUI tool for MySQL (and other databases) that reduces the need to write SQL manually. Download from the official site (paid, with a 14-day trial).

To connect:

• Command line: mysql -h127.0.0.1 -P3306 -uroot -p
• In Navicat: Connection → MySQL → enter host, port, username, password.

The article also includes a full SQL data dump for the exercises (tables: class, course, score, student, teacher).