Đang tải trang
Trong bài này
Một học viên mới đã chọn lớp Database, nhưng hồ sơ của bạn ấy chưa có trong danh sách. Cô Mai cần lưu cả thông tin cá nhân và đăng ký lớp. Nếu chỉ nhìn thấy tên mới, cô chưa thể kết luận rằng bạn ấy đã được xếp lớp đúng.
Hồ sơ phải có trước dòng đăng ký
Trong students, mỗi dòng mô tả một học viên. Trong enrollments, mỗi dòng diễn tả một lần học viên đăng ký vào một lớp. Dòng được tham chiếu (parent row) là dòng mà khóa ngoại trỏ tới, chẳng hạn hồ sơ học viên hoặc lớp học.
Một đăng ký có hai khóa ngoại cần thỏa đồng thời. Giá trị student_id phải có trong students.id, còn class_id phải có trong classes.id. Tạo đăng ký không tự tạo hồ sơ hoặc lớp còn thiếu.
Sơ đồ giúp xác định dữ liệu cần tồn tại trước khi nhập:
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
Cô Mai nhận hồ sơ của Ngọc, mã 9, học khối 7, sống ở Huế. Bạn ấy chưa có xu hoặc người hướng dẫn và chọn lớp mã 3. Ta thêm hồ sơ trước, rồi mới thêm đăng ký:
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 |
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 students(id, name, grade, city, coins, mentor)
VALUES (9, 'Ngọc', 7, 'Huế', 0, NULL);
INSERT INTO enrollments(student_id, class_id, status)
VALUES (9, 3, 'active');
SELECT s.id, s.name, c.name AS class_name, e.status
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
JOIN classes AS c ON c.id = e.class_id
WHERE s.id = 9;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 |
| 9 | 3 | active |
1 dòng
| id | name | class_name | status |
|---|---|---|---|
| 9 | Ngọc | Database | active |
Kết quả ghép hiển thị tên Ngọc, lớp Database và trạng thái active. Tên lớp đến từ bảng classes, không phải do câu thêm ghi lặp lại vào đăng ký. Nhờ giữ mã lớp, đăng ký vẫn trỏ đúng lớp khi lớp được đổi tên.
Thứ tự thêm dựa vào quan hệ, không dựa vào việc bảng nào xuất hiện trước trên màn hình. Nếu cần tạo cả lớp mới và học viên mới, hai dòng đó đều phải có trước đăng ký. Giữa hai dòng không tham chiếu nhau ấy, bạn có thể chọn thứ tự thuận tiện.
Một mã hợp lệ về hình thức vẫn có thể trỏ sai nơi
Kiểu số nguyên chỉ kiểm được dạng giá trị. Khóa ngoại còn kiểm được dòng được trỏ tới có tồn tại hay không. Tuy vậy, một mã tồn tại chưa chắc là lớp mà người học thực sự chọn.
Hãy thử đăng ký học viên mã 2 vào lớp mã 99. Bảng nguồn cho thấy lớp này chưa có:
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 |
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 (2, 99, 'active');Thông báo lỗi
FOREIGN KEY constraint failed
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 |
SQLite báo lỗi khóa ngoại và bảng đăng ký không đổi. Mã 99 có dạng số hợp lệ, nhưng không tham chiếu tới lớp nào. Đổi thành một mã bất kỳ chỉ để hết lỗi có thể làm học viên bị xếp nhầm lớp.
Bạn nên kiểm tên lớp tương ứng trước khi ghi. Với yêu cầu đăng ký lớp Database, hãy đối chiếu mã và tên trong classes. Nếu chưa thấy lớp cần tìm, cần xác minh danh mục thay vì tắt ràng buộc.
| Điều cần kiểm | Bằng chứng cần xem | Điều vẫn chưa biết |
|---|---|---|
| Mã học viên tồn tại | Dòng tương ứng trong students | Người gửi có chọn đúng người không |
| Mã lớp tồn tại | Dòng tương ứng trong classes | Lớp có phù hợp yêu cầu không |
| Chưa trùng đăng ký | Cặp mã trong enrollments | Có cho phép đổi trạng thái không |
Một học viên có thể học nhiều lớp, nên chỉ kiểm riêng student_id là chưa đủ để phát hiện trùng. Khóa đăng ký là cả cặp mã học viên và mã lớp. Bài 24 đã cho bạn công cụ xử lý đúng cặp nếu yêu cầu cho phép cập nhật trạng thái.
Kiểm phần mới và phần phải giữ nguyên
Đối chiếu sau nhập (verification) là so dữ liệu sau thao tác với yêu cầu ban đầu. Bạn cần kiểm phần mới được tạo, đồng thời kiểm phần cũ không bị sửa. Chỉ đếm số dòng sẽ bỏ sót nhiều lỗi.
Ví dụ, tạo được một học viên mới nhưng đổi tên học viên cũ vẫn là nhập sai. Hai bảng cùng có đúng số dòng không chứng minh mọi giá trị đều đúng. Truy vấn kiểm nên chọn những cột liên quan đến yêu cầu.
Giả sử cô Mai đăng ký học viên mã 2 vào Database. Bạn ấy đã có lớp Python, nên lớp cũ cần được giữ nguyên:
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 |
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 (2, 3, 'active');
SELECT s.id, s.name, c.name AS class_name, e.status
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
JOIN classes AS c ON c.id = e.class_id
WHERE s.id = 2
ORDER BY c.id;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 |
2 dòng
| id | name | class_name | status |
|---|---|---|---|
| 2 | Bình | Python | active |
| 2 | Bình | Database | active |
Kết quả có hai lớp của Bình: lớp Python đã có và lớp Database vừa thêm. Điều kiện s.id = 2 giới hạn báo cáo để dễ đọc. Điều kiện này không phải lệnh xóa những đăng ký của người khác.
Khi kiểm toàn bộ thao tác, bạn còn phải xem các đăng ký ngoài phạm vi ấy. Một câu xóa bảng rồi tạo lại vài dòng có thể bắt chước báo cáo nhỏ, nhưng làm mất lịch sử của mọi người. Vì thế, các bài thực hành đối chiếu trạng thái của cả các bảng không được đổi.
- Kiểm dòng mới có đúng mã, tên và các giá trị còn thiếu.
- Kiểm đăng ký trỏ đúng học viên, đúng lớp và đúng trạng thái.
- Kiểm học viên cũ, số dư cũ và các đăng ký cũ không đổi.
Mỗi lần Chạy SQL bắt đầu với dữ liệu mẫu mới. Đặt phần nhập và phần đọc kiểm trong cùng lượt nếu bạn muốn quan sát thay đổi. Không tìm dòng vừa tạo của lượt trước trong lượt mới, vì hai lượt không dùng chung bản dữ liệu.
Nhiều bước nhập có thể để lại một phần kết quả
Hai câu thêm riêng không mặc nhiên tạo thành một thao tác chung. Nếu câu thêm hồ sơ thành công, câu thêm đăng ký có thể vẫn bị lỗi. Trạng thái dang dở (partial state) là khi chỉ một phần công việc đã được lưu.
Hình sau mô tả một quy trình không nhóm các bước lại. Mỗi câu thêm chạy trong lần ghi riêng của SQLite:
Nhập hồ sơHồ sơ hợp lệ được lưuNhập đăng kýLớp không tồn tại gây lỗiSau lỗiHồ sơ còn, đăng ký chưa cóXử lýBáo thiếu và quyết định bước tiếp
Ta dựng riêng tình huống đã lưu hồ sơ bằng một bộ dữ liệu bổ sung. Bộ này chỉ thêm Ngọc mã 9, chưa thêm đăng ký nào. Ví dụ kế tiếp kiểm điều gì còn lại khi bước đăng ký sai:
Câu đăng ký tới lớp không tồn tại bị từ chối, còn hồ sơ nguồn vẫn có mặt:
Dữ liệu đầu vào
students7 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
| 9 | Ngọc |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
INSERT INTO enrollments(student_id, class_id, status)
VALUES (9, 99, 'active');Thông báo lỗi
FOREIGN KEY constraint failed
students7 dòng| id | name | grade | city | coins | mentor |
|---|---|---|---|---|---|
| 1 | An | 6 | Hà Nội | 120 | Cô Mai |
| 2 | Bình | 7 | Đà Nẵng | 90 | NULL |
| 3 | Chi | 8 | Hà Nội | 180 | Thầy Nam |
| 4 | Dung | 6 | Huế | 60 | NULL |
| 5 | Huy | 9 | Cần Thơ | 180 | Cô Mai |
| 6 | Lan | 7 | Hà Nội | 150 | Thầy Nam |
| 9 | Ngọc | 7 | Huế | 0 | NULL |
Bảng sau lỗi vẫn chứa học viên mã 9. Khóa ngoại chặn đăng ký sai nhưng không biết yêu cầu rằng hồ sơ và đăng ký phải cùng thành công. Nếu công việc đòi điều đó, cần nhóm nhiều bước bằng transaction ở bài 27.
Ở bài này, bạn luyện nhập dữ liệu hợp lệ và nhận ra điểm có thể dang dở. Không cần tự thêm cú pháp xử lý lỗi chưa học. Khi làm ứng dụng thật, nhóm phát triển phải quyết định giữ hồ sơ chờ xếp lớp hay hủy cả thao tác.
Khi cô Mai gửi lại một tệp nhập, còn cần phân biệt nhập thiếu với gửi lại dòng đã có. Đăng ký trùng cặp có thể báo lỗi, giữ nguyên hoặc cập nhật theo chính sách đã chọn. Bạn nên hỏi rõ trạng thái mong muốn thay vì mặc định mọi dòng gửi lại đều là đăng ký mới.
Lỗi thường gặp
| Lỗi | Vì sao sai | Cách sửa |
|---|---|---|
| Thêm đăng ký trước hồ sơ | Khóa ngoại không tìm thấy học viên | Tạo dòng được tham chiếu trước |
| Chỉ nhìn thấy tên mới là kết luận đủ | Học viên có thể chưa được đăng ký lớp | Kiểm thêm quan hệ qua JOIN |
| Xóa dòng cũ để nhập lại cho gọn | Làm mất thông tin ngoài yêu cầu | Chỉ thêm hoặc sửa đúng phạm vi |
| Cho rằng lỗi bước sau hủy bước trước | Các câu riêng có thể đã được lưu | Xác định yêu cầu cùng thành công |
Tóm tắt
- Đăng ký chỉ hợp lệ khi cả học viên và lớp được tham chiếu đều tồn tại.
- Khóa ngoại kiểm sự tồn tại, còn người nhập phải kiểm chọn đúng đối tượng.
- Đối chiếu sau nhập bao gồm dữ liệu mới và dữ liệu cũ cần bảo toàn.
- Một bước nhập bị lỗi có thể để lại hồ sơ chờ xử lý từ bước trước.