Bài 12: Khóa chính, khóa ngoại và quan hệ

Cô Mai nhận hai bài kiểm tra cùng ghi tên An. Nếu câu lạc bộ có hai bạn trùng tên, cô cần cách phân biệt người nộp bài. Danh sách lớp cũng phải chỉ rõ mỗi đăng ký thuộc về ai. Tên giúp cô đọc danh sách, còn mã giúp phần mềm phân biệt chính xác từng học viên.

Khóa chính trả lời câu hỏi “đúng dòng nào?”

Bảng students lưu tên để người đọc nhận ra học viên. Cột id lưu mã riêng để phần mềm phân biệt từng học viên. Khóa chính (primary key) là cột hoặc bộ cột xác định duy nhất một dòng. Trong bảng học viên, khóa đó là id.

Để tìm đúng một học viên, trước hết hãy đối chiếu mã với tên trong bảng sau:

SELECT id, name
FROM students
ORDER BY id;

Kết quả có sáu mã khác nhau. Mã 1 xác định An, còn mã 2 xác định Bình. Hai dòng khác nhau không được dùng chung giá trị khóa chính. Muốn tìm An trong danh sách này, bạn dùng điều kiện id = 1.

Các tên trong dữ liệu mẫu đều khác nhau. Điều đó không bảo đảm những học viên đăng ký sau này cũng có tên khác nhau. Một học viên mới có thể cùng tên với người đã có. Tên cũng có thể được sửa khi nhập nhầm.

Vì vậy, bạn cần một mã riêng cho mỗi học viên, ngay cả khi danh sách hiện tại chưa có ai trùng tên.

Mã nên ổn định khi thông tin mô tả thay đổi. Đổi tên học viên không có nghĩa là tạo ra một người khác. Nếu điểm vẫn tham chiếu đúng mã cũ, lịch sử học tập vẫn thuộc về cùng người. Nhờ khóa chính, phần mềm vẫn nhận ra cùng học viên dù tên hoặc thông tin khác đã đổi.

Bạn cũng cần đọc khóa trong phạm vi của đúng bảng. students.id = 1 là mã học viên. scores.id = 1 lại là mã một lần chấm. Hai mã cùng bằng 1 vẫn chỉ hai việc khác nhau: một học viên và một lần chấm.

Khóa ngoại chỉ về dòng được tham chiếu

Khóa ngoại (foreign key) là cột hoặc bộ cột tham chiếu một khóa hợp lệ của bảng được liên kết. Ở đây, scores.student_id tham chiếu students.id. Nhờ mã này, mỗi dòng điểm cho biết ai được chấm mà không cần chép lại tên học viên.

Hãy lấy các lần chấm của học viên mã 1:

SELECT id, student_id, score, day
FROM scores
WHERE student_id = 1
ORDER BY id;

Kết quả có hai dòng với id lần lượt là 1 và 2. Cả hai có student_id = 1, nên đều thuộc về An. Điểm 80 và 90 là hai lần chấm khác nhau. Cột student_id được phép lặp vì một người có thể được chấm nhiều lần.

Điều kiện vừa dùng kiểm tra mã người nhận điểm, không kiểm tra mã lần chấm. Nếu đổi thành WHERE id = 1, bạn chỉ lấy một lần chấm. Đây là lỗi khó thấy khi dữ liệu ban đầu có nhiều cột mã trùng giá trị. Hãy đọc tên cột cùng ý nghĩa của một dòng trước khi viết điều kiện.

Bạn có thể hình dung khóa ngoại như địa chỉ của một dòng đã có. Địa chỉ phải dẫn tới đúng bảng và đúng khóa. Giá trị student_id = 999 không hợp lệ nếu bảng học viên chưa có mã 999. Như vậy, lưu một số nguyên vào cột mã chưa đủ. Mã đó còn phải chỉ tới một học viên có thật.

INSERT thêm một dòng vào bảng. Bạn sẽ học kỹ câu lệnh này ở bài 24. Ở đây, bạn chỉ cần đọc thông báo khi thêm điểm cho mã học viên chưa tồn tại:

INSERT INTO scores (id, student_id, score, day)
VALUES (8, 999, 70, '2026-09-03');

SQLite báo FOREIGN KEY constraint failed, nghĩa là mã tham chiếu không hợp lệ. Mã lần chấm 8 chưa trùng và điểm 70 nằm trong khoảng cho phép. Lỗi xảy ra vì không có học viên mã 999, nên lần chấm này không được thêm.

Liên kết bằng mã giúp bạn chỉ cần lưu tên học viên ở một nơi. Nếu An được sửa tên, bảng điểm vẫn tham chiếu mã 1. Bảng điểm không cần thêm cột tên hoặc sửa từng lần chấm. Việc lấy tên từ bảng liên quan sẽ được học bằng phép nối ở bài 14.

Một học viên có thể có nhiều lần chấm

Quan hệ một–nhiều (one-to-many) cho phép một dòng ở bảng này liên quan tới nhiều dòng ở bảng kia. Với học viên và điểm, một học viên có thể có nhiều lần chấm. Mỗi lần chấm trong mẫu thuộc đúng một học viên.

Hình sau cho thấy mã người được lặp ở bảng điểm, còn mã lần chấm vẫn riêng biệt:

Hình chỉ dùng để đọc liên kết, chưa yêu cầu bạn viết phép nối. Lan không có đường nối tới điểm nào vì bạn ấy chưa được chấm bài. Dữ liệu này vẫn hợp lệ.

Khóa ngoại chỉ yêu cầu mỗi lần chấm phải thuộc một học viên có thật, không yêu cầu học viên nào cũng có điểm. Tạo một điểm 0 giả cho Lan sẽ làm sai lịch sử.

Bạn có thể kiểm tra số lần chấm theo mã người bằng kỹ năng gom nhóm đã học:

SELECT student_id, COUNT(*) AS attempts
FROM scores
GROUP BY student_id
ORDER BY student_id;

Mã 1 và mã 3 đều có hai lần chấm. Những người còn lại trong kết quả có một lần. Mã 6 không xuất hiện vì scores không có dòng nào của Lan để gom nhóm. Sự vắng mặt đó khác với một dòng điểm có giá trị 0.

Khi đếm học viên đã làm bài, bạn cần các mã người khác nhau thay vì mọi mã lần chấm. SELECT DISTINCT student_id trả danh sách mã học viên có điểm, mỗi mã chỉ xuất hiện một lần. Ngược lại, đếm mọi dòng của scores diễn tả số lần chấm.

Cùng bảng điểm có thể cho biết số người đã làm bài hoặc số lần chấm. Bạn cần xác định mình muốn đếm người hay đếm lần chấm.

Hai khóa cùng xác định một đăng ký

Quan hệ nhiều–nhiều (many-to-many) cho phép một học viên có nhiều lớp và một lớp có nhiều học viên. Bảng liên kết (junction table) lưu từng cặp học viên–lớp thành một dòng. Trong bộ mẫu, enrollments đảm nhiệm việc đó.

Đọc các cặp mã đã đăng ký theo thứ tự sau:

SELECT student_id, class_id, status
FROM enrollments
ORDER BY student_id, class_id;

Hai dòng đầu là cặp (1, 1) và (1, 3). Cả hai thuộc An, nhưng trỏ tới hai lớp khác nhau. Đây là hai quan hệ hợp lệ. Nếu yêu cầu riêng student_id không được lặp, bạn sẽ chỉ lưu được một lớp cho mỗi học viên.

Khóa chính ghép (composite primary key) dùng nhiều cột cùng nhau để xác định dòng. Ở enrollments, khóa là cặp (student_id, class_id). Từng cột có thể lặp, nhưng cả cặp không được lặp. Cột status mô tả trạng thái của chính đăng ký đó.

Nếu cô Mai hỏi “ai đã đăng ký?”, ta chỉ cần các mã học viên khác nhau:

SELECT DISTINCT student_id
FROM enrollments
ORDER BY student_id;

Kết quả chỉ còn năm mã người. Nó phù hợp với câu hỏi ai đã đăng ký, nhưng không thể cho biết An học những lớp nào. Hai đăng ký của An chỉ còn được thể hiện bằng một mã học viên là 1. Khi đề yêu cầu các đăng ký, hãy lấy đủ hai cột của cặp khóa.

Nếu thêm lại cặp (1, 1) với trạng thái khác, khóa chính vẫn chặn đăng ký trùng:

INSERT INTO enrollments (student_id, class_id, status)
VALUES (1, 1, 'paused');

Lỗi ở cặp cột enrollments.student_id, enrollments.class_id cho biết cặp mã học viên–lớp phải duy nhất. Thông báo đầy đủ của SQLite là UNIQUE constraint failed: enrollments.student_id, enrollments.class_id. Cặp (1, 1) đã có trong bảng, nên trạng thái paused không giúp tạo thêm đăng ký cho cùng cặp.

Khóa chính chặn việc đăng ký lại cùng cặp học viên–lớp. Hai khóa ngoại bảo đảm cả học viên và lớp của đăng ký đều tồn tại.

Lỗi thường gặp

Tóm tắt

• Khóa chính giúp xác định đúng một dòng trong bảng chứa khóa đó.
• Khóa ngoại lưu mã liên quan. Tên hiển thị nằm trong bảng được tham chiếu.
• Trong quan hệ một–nhiều, nhiều lần chấm có thể cùng lưu mã của một học viên.
• Trong quan hệ nhiều–nhiều, bảng liên kết lưu từng cặp học viên–lớp và dùng cả cặp làm khóa.

Kiểm tra nhanh

Đọc thêm

• SQLite giải thích khóa ngoại và việc bật kiểm tra tham chiếu.
• SQLite mô tả khóa chính trong cấu trúc bảng.