Đang tải trang
Trong bài này
Cô Mai mở thêm lớp cho câu lạc bộ. Một học viên được học nhiều lớp, và mỗi lớp nhận nhiều học viên. Tuy nhiên, nếu bấm đăng ký hai lần, bạn ấy chỉ được có một chỗ trong cùng lớp. Cô cần cơ sở dữ liệu phân biệt đăng ký mới với việc gửi lại đăng ký cũ.
Khóa của đăng ký là cả cặp học viên và lớp
Trong bảng đăng ký, một dòng là một lần một học viên ghi danh vào một lớp. Khóa chính ghép (composite primary key) dùng nhiều cột cùng nhau để xác định dòng. Hai dòng chỉ trùng khóa khi mọi thành phần tương ứng của khóa đều bằng nhau.
Hãy xem các cặp mã trong dữ liệu chung. Hai dòng được tô thuộc cùng một học viên nhưng khác lớp:
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Cặp (1, 1) nghĩa là học viên mã 1 đăng ký lớp mã 1. Cặp (1, 3) vẫn thuộc học viên ấy nhưng là đăng ký khác. Vì vậy, giá trị của student_id được lặp, và giá trị của class_id cũng được lặp.
Sơ đồ cho thấy mỗi đăng ký nối tới một học viên và một lớp. Khóa ghép bảo vệ cặp nối, còn hai khóa ngoại bảo vệ hai đầu của quan hệ:
enrollments7 dòng
PKFKstudent_idINTEGER
PKFKclass_idINTEGER
status= 'active'TEXT
students6 dòng
PKidINTEGER
nameTEXT
grade6–12INTEGER
cityTEXT
coins≥ 0INTEGER
mentorcó thể NULLTEXT
classes3 dòng
PKidINTEGER
nameduy nhấtTEXT
classes3 dòng
PKidINTEGER
nameduy nhấtTEXT
enrollments7 dòng
PKFKstudent_idINTEGER→ students.id
PKFKclass_idINTEGER→ classes.id
status= 'active'TEXT
students6 dòng
PKidINTEGER
nameTEXT
grade6–12INTEGER
cityTEXT
coins≥ 0INTEGER
mentorcó thể NULLTEXT
Bình đang có cặp (2, 1) nhưng chưa có cặp (2, 3). Câu sau thêm lớp thứ hai cho Bình và hiện bảng sau thay đổi:
Dữ liệu đầu vào
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Câu lệnh
INSERT INTO enrollments(student_id, class_id)
VALUES (2, 3);Bảng enrollments sau khi chạy
enrollments5 dòng thay đổi| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 2 | 3 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Dòng (2, 3) được thêm vì cả cặp chưa tồn tại. Nếu đặt thêm UNIQUE(student_id), Bình sẽ không được học lớp thứ hai. Nếu đặt UNIQUE(class_id), mỗi lớp lại chỉ nhận được một người. Ràng buộc quá chặt cũng làm sai yêu cầu, dù nó chặn được đăng ký trùng.
Ngược lại, Chi đã có cặp (3, 2). Thử thêm lại cặp ấy với trạng thái khác:
Dữ liệu đầu vào
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Câu lệnh
INSERT INTO enrollments(student_id, class_id, status)
VALUES (3, 2, 'paused');Thông báo lỗi
enrollments.student_id, enrollments.class_id
SQLite báo lỗi duy nhất vì khóa vẫn là (3, 2). Cột status không thuộc khóa, nên đổi trạng thái không tạo danh tính mới. Muốn sửa trạng thái của đăng ký đang có, bạn cần thao tác sửa dòng bằng UPDATE, sẽ học ở bài 26.
Bắt buộc có giá trị khác với giới hạn giá trị
Ràng buộc (constraint) là quy tắc cơ sở dữ liệu kiểm khi dữ liệu được ghi. NOT NULL yêu cầu có giá trị. CHECK kiểm một điều kiện, chẳng hạn trạng thái chỉ được là 'active' hoặc 'paused'. Hai quy tắc này trả lời hai câu hỏi khác nhau.
Để thử riêng từng quy tắc, ta dùng bảng phụ status_demo. Bảng chỉ có mã và trạng thái, ban đầu chứa một dòng hợp lệ. Phần chuẩn bị dưới đây tạo dữ liệu riêng cho các ví dụ có setup=status-check:
Trước hết, chỉ có CHECK thì chuyện gì xảy ra khi gửi NULL?
Dữ liệu đầu vào
status_demo1 dòng| id | status |
|---|---|
| 1 | active |
Câu lệnh
INSERT INTO status_demo(id, status)
VALUES (2, NULL);Bảng status_demo sau khi chạy
status_demo1 dòng thay đổi| id | status |
|---|---|
| 1 | active |
| 2 | NULL |
Dòng mới có status là NULL. Trong SQLite, CHECK chỉ từ chối khi biểu thức cho kết quả bằng không. Biểu thức kiểm trạng thái ở đây cho kết quả NULL, nên dòng được nhận. Muốn cấm giá trị thiếu, cần ghi rõ NOT NULL.
Một trạng thái nằm ngoài danh sách lại bị chặn. Hãy thử thêm 'waiting' vào cùng bảng mẫu:
Dữ liệu đầu vào
status_demo1 dòng| id | status |
|---|---|
| 1 | active |
Câu lệnh
INSERT INTO status_demo(id, status)
VALUES (2, 'waiting');Thông báo lỗi
status IN ('active', 'paused')
Lỗi này khác lỗi thiếu giá trị. Giá trị 'waiting' có tồn tại nhưng không được phép dùng. Bảng sau giúp chọn đúng quy tắc thay vì thêm ràng buộc theo cảm giác:
| Cần bảo vệ điều gì? | Quy tắc | Điều quy tắc chưa bảo vệ |
|---|---|---|
| Mỗi dòng phải có trạng thái | NOT NULL | Chưa giới hạn các chuỗi được nhập |
| Trạng thái thuộc danh sách | CHECK(status IN ('active', 'paused')) | Chưa cấm NULL |
| Cặp người và lớp không lặp | PRIMARY KEY(student_id, class_id) | Chưa giới hạn trạng thái |
Giá trị mặc định chỉ điền khi bạn bỏ cột
Giá trị mặc định (default value) là giá trị cơ sở dữ liệu dùng khi câu thêm dòng không cung cấp cột đó. Trong dữ liệu chung, enrollments.status có DEFAULT 'active' và NOT NULL. Mặc định giúp nhập gọn hơn nhưng không sửa mọi dữ liệu sai.
Lan chưa đăng ký lớp nào. Câu sau chỉ cung cấp hai mã, bỏ hẳn cột trạng thái khỏi danh sách:
Dữ liệu đầu vào
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Câu lệnh
INSERT INTO enrollments(student_id, class_id)
VALUES (6, 2);Bảng enrollments sau khi chạy
enrollments1 dòng thay đổi| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
| 6 | 2 | active |
Cặp (6, 2) xuất hiện với trạng thái 'active'. Giá trị ấy đến từ mặc định của cột, không phải từ một lần đăng ký trước của Lan. Các đăng ký khác giữ nguyên vì câu lệnh chỉ thêm một cặp mới.
Nếu cung cấp cột trạng thái và truyền NULL, bạn đang yêu cầu lưu giá trị thiếu. Thử cách viết này trên bản dữ liệu gốc:
Dữ liệu đầu vào
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Câu lệnh
INSERT INTO enrollments(student_id, class_id, status)
VALUES (6, 2, NULL);Thông báo lỗi
enrollments.status
SQLite từ chối vì status có NOT NULL. Cơ sở dữ liệu không xem NULL là yêu cầu dùng mặc định. Khi thiết kế biểu mẫu, cần phân biệt người dùng chưa chọn với người dùng chọn một giá trị cụ thể.
Khóa ngoại bảo vệ liên kết cả lúc thêm và lúc xóa
Khóa ngoại (foreign key) yêu cầu mã được tham chiếu phải tồn tại trong bảng đích. Với đăng ký, student_id trỏ tới students.id, còn class_id trỏ tới classes.id. Phòng thực hành đã bật kiểm khóa ngoại cho từng lần chạy.
Bảng nguồn dưới đây có Bình với mã học viên 2, nhưng không có lớp mã 99. Vì thế, câu thêm đăng ký này không thể tạo liên kết hợp lệ:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
INSERT INTO enrollments(student_id, class_id)
VALUES (2, 99);Thông báo lỗi
FOREIGN KEY constraint failed
Lỗi xảy ra dù cặp (2, 99) chưa bị trùng. Khóa chính chỉ kiểm danh tính đăng ký, còn khóa ngoại trên class_id kiểm lớp được chọn có tồn tại. Muốn mỗi đăng ký bắt buộc thuộc một lớp, cột tham chiếu còn cần NOT NULL.
Khóa ngoại cũng ngăn việc để lại dòng con mất liên kết. An đang có đăng ký, điểm và lịch sử xu. Hãy thử xóa dòng học viên của An:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
enrollments7 dòng| student_id | class_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
Câu lệnh
DELETE FROM students
WHERE id = 1;Thông báo lỗi
FOREIGN KEY constraint failed
Câu lệnh thất bại thay vì để các mã tham chiếu mất đích. Một lựa chọn khác là ON DELETE CASCADE, nghĩa là tự xóa dòng con khi xóa dòng cha. Lựa chọn ấy chỉ phù hợp khi nghiệp vụ thực sự muốn xóa các dòng con.
Với điểm và lịch sử thưởng, cần quyết định cách giữ lịch sử trước. Khi kiểm thiết kế, hãy thử cả trường hợp được phép lẫn trường hợp phải bị chặn.
Lỗi thường gặp
| Lỗi | Vì sao sai | Cách sửa |
|---|---|---|
Thêm UNIQUE(student_id) vào đăng ký | Một người bị giới hạn chỉ một lớp | Dùng khóa trên cả cặp mã |
Chỉ viết CHECK rồi mong cấm NULL | SQLite chấp nhận biểu thức kiểm cho kết quả NULL | Thêm NOT NULL nếu giá trị bắt buộc |
Gửi NULL để lấy mặc định | Mặc định không thay giá trị đã gửi | Bỏ cột nếu muốn dùng mặc định |
| Chọn xóa dây chuyền chỉ để hết lỗi | Có thể mất điểm và lịch sử cần giữ | Xác định yêu cầu giữ lịch sử trước |
Tóm tắt
- Khóa ghép xét toàn bộ cặp giá trị, cho phép từng thành phần xuất hiện nhiều lần.
- Quy tắc tốt phải nhận cả dữ liệu hợp lệ, không chỉ chặn dữ liệu sai.
NOT NULL,CHECK,DEFAULTvà khóa ngoại giải quyết các yêu cầu riêng.- Chọn cách xóa dữ liệu cần dựa vào yêu cầu giữ điểm và lịch sử thưởng.