Bài 8. Khóa chính, khóa ngoại và thiết kế quan hệ

Khái niệm và lý thuyết

Khóa ngoại bảo đảm toàn vẹn tham chiếu: không thể thêm sinh viên vào lớp không tồn tại, không thể xóa lớp đang có sinh viên (trừ khi khai báo CASCADE). Quan hệ: 1-n (một lớp nhiều sinh viên: FK đặt ở bảng “nhiều”), n-n (sinh viên học nhiều môn, môn có nhiều sinh viên: cần bảng trung gian diem chứa hai FK), 1-1 (sinh viên và hồ sơ chi tiết: FK UNIQUE).

Khóa chính

CREATE TABLE lop (
  id INT AUTO_INCREMENT PRIMARY KEY, ...
);
-- Khóa chính kép (bảng trung gian không cần id riêng)
CREATE TABLE dang_ky (
  sinhvien_id INT, monhoc_id INT, hoc_ky VARCHAR(10),
  PRIMARY KEY (sinhvien_id, monhoc_id, hoc_ky)
);
ALTER TABLE monhoc ADD PRIMARY KEY (id);   -- thêm sau

Khóa ngoại và hành vi

CREATE TABLE sinhvien (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ho_ten VARCHAR(100) NOT NULL,
  lop_id INT,
  CONSTRAINT fk_sv_lop FOREIGN KEY (lop_id) REFERENCES lop(id)
    ON DELETE SET NULL        -- xóa lớp: sinh viên còn, lop_id thành NULL
    ON UPDATE CASCADE         -- đổi id lớp: tự cập nhật
);
CREATE TABLE diem (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sinhvien_id INT NOT NULL,
  monhoc_id INT NOT NULL,
  diem DECIMAL(4,2),
  FOREIGN KEY (sinhvien_id) REFERENCES sinhvien(id) ON DELETE CASCADE,   -- xóa SV: xóa điểm theo
  FOREIGN KEY (monhoc_id)  REFERENCES monhoc(id)   ON DELETE RESTRICT,   -- không cho xóa môn còn điểm
  UNIQUE (sinhvien_id, monhoc_id)                                        -- mỗi SV mỗi môn một điểm
);
ALTER TABLE sinhvien ADD CONSTRAINT fk_sv_lop FOREIGN KEY (lop_id) REFERENCES lop(id);
ALTER TABLE sinhvien DROP FOREIGN KEY fk_sv_lop;

Quan hệ n-n

-- Sinh viên <-> Môn học qua bảng diem (hoặc dang_ky)
sinhvien (id, ho_ten)        monhoc (id, ten_mon)
            \                    /
             diem (sinhvien_id, monhoc_id, diem, hoc_ky)

-- Câu hỏi "An học môn gì?" -> JOIN qua bảng trung gian
SELECT m.ten_mon FROM monhoc m
JOIN diem d ON d.monhoc_id = m.id
JOIN sinhvien sv ON sv.id = d.sinhvien_id
WHERE sv.ho_ten = 'Lò Văn An';

Chỉ mục (INDEX)

CREATE INDEX idx_sv_ho_ten ON sinhvien (ho_ten);
CREATE INDEX idx_diem_sv_mon ON diem (sinhvien_id, monhoc_id);
SHOW INDEX FROM sinhvien;
EXPLAIN SELECT * FROM sinhvien WHERE ho_ten = 'Lò Văn An';   -- xem có dùng chỉ mục không
DROP INDEX idx_sv_ho_ten ON sinhvien;

Chỉ mục như mục lục sách: tìm kiếm, JOIN, ORDER BY nhanh hơn nhiều với bảng lớn, đổi lại INSERT/UPDATE chậm hơn chút và tốn dung lượng. Khóa chính, UNIQUE, khóa ngoại tự có chỉ mục. Đánh chỉ mục cho cột hay dùng trong WHERE/JOIN.

Chuẩn hóa cơ bản

Dấu hiệu cần tách bảng: cùng một thông tin được gõ lặp ở nhiều dòng (tên lớp ở mọi sinh viên). Tách thành bảng riêng và tham chiếu bằng FK.