FOREIGN KEY trong MySQL

Tình huống mở đầu

Bảng orders của cửa hàng shop được tạo từ ba năm trước. Trong lệnh CREATE TABLE có hẳn một dòng FOREIGN KEY (customer_id) REFERENCES customers (id), nên cả team yên tâm rằng không đơn nào trỏ tới khách không tồn tại. Hôm nay kế toán đối soát và tìm ra 37 đơn có customer_id không khớp khách nào.

Không có lệnh nào báo lỗi trong ba năm qua. Không ai tắt khóa ngoại, không ai chạy lệnh lạ. Dòng FOREIGN KEY vẫn nằm đó trong file SQL.

Hãy đoán trước: điều gì khiến một dòng khai báo khóa ngoại không bảo vệ được gì, và bảng này đang dùng gì khác với bảng bình thường? Cuối bài bạn sẽ tái hiện đúng lỗi này và biết cách kiểm tra bảng của mình.

Ôn nhanh bài trước: bảng có khóa chính CHAR(36) thì chỉ mục phụ trên cột sku thay đổi thế nào so với khóa chính INT?

Đáp án: lớn hơn, vì mỗi mục của chỉ mục phụ mang theo giá trị khóa chính. Khóa ngoại cũng trỏ vào khóa chính đó, nên bài này tiếp tục câu chuyện: bảng con bám vào bảng cha ra sao, và MySQL thi hành sự bám đó như thế nào.

Mục tiêu bài học

Sau bài này bạn có thể:

• Giải thích vì sao khóa ngoại chỉ có hiệu lực với InnoDB, và kiểm tra một bảng có thật sự bị ràng buộc không.
• Đọc và sửa các lỗi khi khai báo khóa ngoại: kiểu cột không tương thích (3780), bảng cha không tồn tại (1824).
• Gắn khóa ngoại vào bảng đang có dữ liệu: tìm dòng mồ côi, dọn, rồi ADD CONSTRAINT.
• Dự đoán điều gì xảy ra với dữ liệu khi foreign_key_checks bị tắt rồi bật lại.

Bức tranh lớn

Chương 6 đã dạy khóa ngoại và các hành động ON DELETE. Bài này không nhắc lại, mà hỏi điều khác: MySQL thi hành khóa ngoại ở đâu, khi nào, và lúc nào nó im lặng không làm gì. Ẩn dụ xuyên suốt: khóa ngoại là một tấm thẻ giới thiệu, mỗi đơn hàng cầm thẻ ghi số khách. InnoDB là bảo vệ cửa thật sự kiểm tra thẻ. Còn khai báo khóa ngoại trên bảng MyISAM chỉ là tấm biển "có camera" treo trên tường mà camera không nối điện. Dự án xuyên suốt là cặp bảng customers và orders của cửa hàng shop.

Hình dưới trả lời một câu hỏi: khi một lệnh ghi vào cột khóa ngoại, những điều kiện nào quyết định nó bị kiểm tra, bỏ qua hay từ chối?

Cách đọc hình: đi từ ô xanh dương xuống qua ba ô cam, là ba câu hỏi liên tiếp. Câu đầu hỏi cả hai bảng có phải InnoDB không: nếu không thì mệnh đề khóa ngoại bị bỏ, không có kiểm tra nào (ô đỏ bên trái). Câu thứ hai hỏi foreign_key_checks có bằng 1 không: nếu không thì dòng vào bảng dù có mồ côi (ô đỏ giữa). Câu thứ ba hỏi bảng cha có dòng khớp không: có thì nhận (ô xanh lá), không thì lỗi 1452 (ô đỏ cuối). Chỉ một trong ba đường dẫn tới an toàn.

Chỗ ẩn dụ không còn đúng: bảo vệ cửa thật chỉ kiểm tra người đi vào. Khóa ngoại InnoDB còn canh cả chiều ngược lại, từ chối xóa hoặc sửa khách khi đơn còn trỏ tới.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. Khóa ngoại là tính năng của InnoDB

Chỉ InnoDB lưu và thi hành khóa ngoại. Trên engine MyISAM, MySQL vẫn chấp nhận cú pháp FOREIGN KEY nhưng bỏ mệnh đề đó, không báo lỗi, không báo cảnh báo trong kết quả của CREATE TABLE. Bảng cha và bảng con đều phải là InnoDB.

2. Cột khóa ngoại phải tương thích và có chỉ mục

Cột con và cột cha cần cùng kiểu (cùng INT hoặc cùng BIGINT, cùng UNSIGNED hay không). Lệch kiểu là lỗi 3780. Nếu cột con chưa có chỉ mục, MySQL tự tạo một chỉ mục, và nếu bạn không đặt tên thì tự sinh tên ràng buộc dạng tênbảng_ibfk_N.

3. Gắn khóa ngoại vào bảng đã có dữ liệu

ALTER TABLE ... ADD CONSTRAINT kiểm tra mọi dòng hiện có và thất bại (lỗi 1452) nếu còn dòng mồ côi. Việc đúng là tìm dòng mồ côi bằng LEFT JOIN ... IS NULL, xử lý chúng, rồi mới gắn. Từ phía cha, InnoDB cũng không cho DROP TABLE (3730) hoặc TRUNCATE (1701) bảng cha đang được tham chiếu.

4. foreign_key_checks và dữ liệu mồ côi

Biến foreign_key_checks tắt thì mọi lệnh trong phiên không bị kiểm tra. Đây là công cụ hợp lệ để nạp dữ liệu theo thứ tự lộn xộn, nhưng bật lại không quét lại dữ liệu. Dòng mồ côi nạp lúc tắt ở lại mãi cho đến khi bạn tự tìm ra.

Ví dụ đầu tiên

Tái hiện tình huống mở đầu. Bảng cha customers là InnoDB với hai khách. Bảng con orders_my khai báo khóa ngoại nhưng dùng engine MyISAM [Đã chạy: MySQL 8.0.44]:

CREATE TABLE orders_my (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=MyISAM;

INSERT INTO orders_my VALUES (1, 999);   -- khách 999 không tồn tại
SELECT * FROM orders_my;
SHOW CREATE TABLE orders_my\G

Dự đoán trước khi xem: INSERT đơn trỏ tới khách 999 có bị từ chối không, và SHOW CREATE TABLE còn dòng CONSTRAINT không?

+----+-------------+
| id | customer_id |
+----+-------------+
|  1 |         999 |
+----+-------------+
*************************** 1. row ***************************
       Table: orders_my
Create Table: CREATE TABLE `orders_my` (
  `id` int NOT NULL,
  `customer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Lệnh INSERT thành công, đơn mồ côi nằm trong bảng, và SHOW CREATE TABLE không còn dòng CONSTRAINT nào: MySQL đã bỏ mệnh đề khóa ngoại ngay lúc tạo bảng. Chỉ còn lại một chỉ mục KEY customer_id. Bây giờ làm đúng cách với InnoDB:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB;

INSERT INTO orders VALUES (1, 2);        -- khách 2 có thật
INSERT INTO orders VALUES (2, 999);      -- khách 999 không tồn tại
SELECT * FROM orders;
SHOW CREATE TABLE orders\G

Dự đoán trước khi xem: hai lệnh INSERT cho kết quả gì, và bảng orders còn mấy dòng?

+----+-------------+
| id | customer_id |
+----+-------------+
|  1 |           2 |
+----+-------------+
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `id` int NOT NULL,
  `customer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_orders_customer` (`customer_id`),
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ERROR 1452 (23000) at line 11: Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Thông báo lỗi được công cụ chạy gom lại ở cuối, dù nó xảy ra ở lệnh INSERT thứ hai. Tên database trong thông báo là database tạm của máy chạy bài.

Mẹo: kiểm tra nhanh một bảng bất kỳ có khóa ngoại thật không bằng SHOW CREATE TABLE. Không thấy dòng CONSTRAINT ... FOREIGN KEY thì không có ràng buộc, bất kể file SQL gốc viết gì.

Xem từng bước

Phần khó nhất của bài là foreign_key_checks. Animation dưới đây cho thấy cổng khóa ngoại bị tắt rồi bật lại, và dòng mồ côi ở lại thế nào. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Chuỗi lệnh thật của bước nạp, bảng orders đã có khóa ngoại và đang trỏ tới customers [Đã chạy: MySQL 8.0.44]:

SET foreign_key_checks = 0;
DELETE FROM customers WHERE id = 1;           -- đơn 1 vẫn trỏ tới khách này
INSERT INTO orders VALUES (9, 50, 700000);    -- khách 50 không tồn tại
SET foreign_key_checks = 1;                   -- bật lại kiểm tra

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY o.id;

+----+-------------+
| id | customer_id |
+----+-------------+
|  1 |           1 |
|  9 |          50 |
+----+-------------+

Hai đơn mồ côi: đơn 1 trỏ tới khách đã xóa, đơn 9 trỏ tới khách chưa từng tồn tại. Sau khi bật lại cổng, UPDATE cột total của đơn 9 vẫn qua [Đã chạy: MySQL 8.0.44]:

UPDATE orders SET total = 5 WHERE id = 9;
SELECT id, customer_id, total FROM orders WHERE id = 9;

+----+-------------+-------+
| id | customer_id | total |
+----+-------------+-------+
|  9 |          50 |     5 |
+----+-------------+-------+

Còn UPDATE cột khóa ngoại của đúng dòng đó thì bị chặn:

UPDATE orders SET customer_id = 51 WHERE id = 9;

ERROR 1452 (23000) at line 11: Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Bảng trace bằng chữ:

Bạn vừa làm được: dự đoán dữ liệu còn lại sau một đợt nạp tắt foreign_key_checks, và tìm các dòng mồ côi bằng LEFT JOIN ... IS NULL.

Concept Check

Bảng customers (khách 1, 2) và orders ở trên.

Câu 1. Bảng orders_my dùng MyISAM, khai báo FOREIGN KEY (customer_id) REFERENCES customers (id). INSERT INTO orders_my VALUES (1, 999) cho kết quả nào?

• A. Lỗi 1452, vì khách 999 không tồn tại.
• B. Thành công, vì MyISAM bỏ mệnh đề khóa ngoại khi tạo bảng.
• C. Thành công kèm cảnh báo 1452, vì MyISAM chỉ cảnh báo thay cho lỗi.
• D. Lỗi 3780, vì MyISAM không tương thích với InnoDB.

Câu 2. Bảng o_big có customer_id BIGINT và khai báo khóa ngoại trỏ tới customers.id kiểu INT. Điều gì xảy ra?

• A. Thành công, vì BIGINT chứa được mọi giá trị INT nên tương thích.
• B. Thành công, nhưng MySQL tự đổi customer_id thành INT.
• C. Lỗi 1452 ở lần INSERT đầu tiên có giá trị lớn hơn 2 tỷ.
• D. Lỗi 3780 ngay khi tạo bảng, vì hai cột không tương thích.

Câu 3. Tắt foreign_key_checks, nạp một đơn mồ côi, bật lại. Đơn mồ côi đó có bị MySQL báo lỗi hay xóa không?

• A. Có, MySQL quét lại bảng khi bật lại và xóa đơn mồ côi.
• B. Có, MySQL báo lỗi 1452 ngay lệnh SET foreign_key_checks = 1.
• C. Không, đơn ở lại bảng cho tới khi bạn tự tìm và xử lý.
• D. Không, nhưng đơn bị đánh dấu ẩn để không xuất hiện trong SELECT.

Đáp án và giải thích:

• Câu 1: B. A áp dụng cho InnoDB, không phải MyISAM. C bịa ra cảnh báo không tồn tại. D nhầm: lỗi 3780 chỉ xuất hiện khi hai cột lệch kiểu.
• Câu 2: D. A nghĩ về giá trị thay vì định nghĩa kiểu: MySQL so sánh kiểu cột, không so sánh khoảng giá trị. B và C bịa ra hành vi tự đổi kiểu hoặc kiểm tra muộn.
• Câu 3: C. A và B giả định có bước quét lại, nhưng bật lại chỉ có hiệu lực cho các lệnh sau đó. D bịa ra cơ chế ẩn dòng.

Làm cùng mình

Kho dữ liệu cũ của shop có bảng orders không khóa ngoại, 4 đơn, trong đó đơn 3 trỏ tới khách 7 và đơn 4 trỏ tới khách 8, hai khách không tồn tại. Bạn sẽ gắn khóa ngoại vào bảng này.

Bước 1. Vẽ ra giấy

Trước khi gõ lệnh, vẽ bảng orders bốn dòng và đánh dấu dòng nào mồ côi. Dự đoán lệnh ALTER TABLE ... ADD CONSTRAINT chạy ngay lúc này cho kết quả gì.

Bước 2. Điền chỗ trống

Điền hai chỗ trống: một điều kiện lọc để tìm đơn mồ côi, và mệnh đề thêm khóa ngoại:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
-- TODO 1: giữ lại chỉ những đơn không tìm thấy khách tương ứng
;

ALTER TABLE orders
  -- TODO 2: thêm khóa ngoại fk_orders_customer từ customer_id sang customers (id)
;

Gợi ý cho chỗ trống thứ nhất: dòng không có bên bảng cha thì cột của c là NULL, nên dùng WHERE c.id IS NULL. Gợi ý cho chỗ trống thứ hai: ADD CONSTRAINT <tên> FOREIGN KEY (<cột con>) REFERENCES <bảng cha> (<cột cha>).

Bước 3. Chạy thử lệnh khi chưa dọn dữ liệu

Chạy ALTER TABLE trên bảng còn nguyên hai đơn mồ côi [Đã chạy: MySQL 8.0.44, tên bảng tạm trong thông báo là ví dụ và có thể khác nhau]:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);

ERROR 1452 (23000) at line 6: Cannot add or update a child row: a foreign key constraint fails (`shop`.`#sql-1_21c2`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Truy vấn tìm đơn mồ côi:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

+----+-------------+
| id | customer_id |
+----+-------------+
|  3 |           7 |
|  4 |           8 |
+----+-------------+

Bước 4. Vẽ lại ra giấy

Với hai đơn mồ côi này, bạn có ba lựa chọn: xóa, chuyển sang bảng chờ xử lý, hoặc tạo khách còn thiếu. Viết một câu cho mỗi lựa chọn nêu khi nào nên dùng. Đáp án gợi ý ở mục Lời giải và đáp án.

Thử phá nó đi

Năm tình huống hay gặp quanh khóa ngoại trên MySQL [Đã chạy: MySQL 8.0.44]:

Lỗi lệch kiểu cột, ba biến thể cùng lúc:

CREATE TABLE o_big (id INT PRIMARY KEY, customer_id BIGINT,       CONSTRAINT fk_big FOREIGN KEY (customer_id) REFERENCES customers (id));
CREATE TABLE o_str (id INT PRIMARY KEY, customer_id VARCHAR(10),  CONSTRAINT fk_str FOREIGN KEY (customer_id) REFERENCES customers (id));
CREATE TABLE o_uns (id INT PRIMARY KEY, customer_id INT UNSIGNED, CONSTRAINT fk_uns FOREIGN KEY (customer_id) REFERENCES customers (id));

ERROR 3780 (HY000) at line 3: Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'fk_big' are incompatible.
ERROR 3780 (HY000) at line 4: Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'fk_str' are incompatible.
ERROR 3780 (HY000) at line 5: Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'fk_uns' are incompatible.

Bảo vệ từ phía bảng cha:

DROP TABLE customers;
TRUNCATE TABLE customers;

ERROR 3730 (HY000) at line 8: Cannot drop table 'customers' referenced by a foreign key constraint 'fk_orders_customer' on table 'orders'.
ERROR 1701 (42000) at line 9: Cannot truncate a table referenced in a foreign key constraint (`shop`.`orders`, CONSTRAINT `fk_orders_customer`)

Bảng cha không tồn tại:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES nobody (id)
);

ERROR 1824 (HY000) at line 2: Failed to open the referenced table 'nobody'

Hình dưới đối chiếu mô hình sai với mô hình đúng về foreign_key_checks.

Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai: bật lại kiểm tra là dữ liệu tự lành. Chuỗi xanh là thực tế: cổng chỉ canh những dòng được ghi sau đó, nên dòng mồ côi cũ phải được tìm bằng một truy vấn.

Tự tay làm

Dùng chung cặp bảng customers và orders của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Đọc tên ràng buộc và chỉ mục tự sinh. Đầu vào: bảng customers có sẵn. Tạo orders(id INT PRIMARY KEY, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (id)) mà không đặt tên ràng buộc, rồi chạy SHOW CREATE TABLE orders. Tiêu chí đạt: nêu đúng tên ràng buộc MySQL sinh ra và tên chỉ mục tự thêm trên customer_id. Gợi ý: tên ràng buộc có hậu tố _ibfk_ và một số thứ tự.

Vận dụng

Bài 2. Kiểm kê khóa ngoại của database. Đầu vào: customers, orders (khóa ngoại ON DELETE RESTRICT) và order_items (khóa ngoại tới orders với ON DELETE CASCADE). Viết một câu SELECT trên information_schema.REFERENTIAL_CONSTRAINTS liệt kê tên ràng buộc, bảng con, bảng cha và quy tắc xóa. Tiêu chí đạt: hai dòng, mỗi dòng đúng quy tắc. Gợi ý: lọc CONSTRAINT_SCHEMA = DATABASE().

Thử thách (tùy chọn)

Bài 3. Nạp theo thứ tự ngược mà không để lại mồ côi. Đầu vào: customers và orders đều trống, orders đã có khóa ngoại. Viết đoạn lệnh tắt foreign_key_checks, nạp đơn trước rồi nạp khách sau, bật lại, và kết thúc bằng một truy vấn đếm đơn mồ côi. Tiêu chí đạt: kết quả đếm bằng 0, và bạn giải thích được vì sao truy vấn cuối không thể bỏ. Gợi ý: nạp hai đơn trỏ tới khách 1 và 2, rồi nạp khách 1 và 2.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) Vì sao khai báo FOREIGN KEY trên bảng MyISAM không báo lỗi mà cũng không bảo vệ gì?
2. (Mục tiêu 1, vận dụng) Bạn nhận một file SQL cũ. Lệnh nào cho biết bảng có khóa ngoại thật hay không?
3. (Mục tiêu 2, dự đoán) Cột con là INT UNSIGNED, cột cha là INT. CREATE TABLE với khóa ngoại cho kết quả gì?
4. (Mục tiêu 3, tìm lỗi) ALTER TABLE ... ADD CONSTRAINT báo lỗi 1452 dù bảng cha tồn tại. Nguyên nhân thường gặp là gì, và truy vấn nào tìm ra?
5. (Mục tiêu 4, giải thích) Sau khi bật lại foreign_key_checks, dòng mồ côi cũ có bị chặn khi UPDATE cột không phải khóa ngoại không? Giải thích bằng hai câu.

Điều cần nhớ

• Chỉ InnoDB thi hành khóa ngoại, MyISAM chấp nhận cú pháp rồi bỏ; hãy kiểm tra bằng SHOW CREATE TABLE.
• Cột con và cột cha phải cùng kiểu (lệch là lỗi 3780); MySQL tự tạo chỉ mục cho cột con và tự đặt tên ràng buộc nếu bạn không đặt.
• Gắn khóa ngoại vào bảng có dữ liệu: tìm dòng mồ côi bằng LEFT JOIN ... IS NULL, xử lý, rồi ADD CONSTRAINT.
• Bảng cha đang được tham chiếu không bị DROP (3730) hay TRUNCATE (1701).
• Bật lại foreign_key_checks không quét lại dữ liệu; mồ côi nạp lúc tắt phải tự tìm.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho bài sau: bảng order_items có khóa chính gồm hai cột (order_id, line). Khóa ngoại trỏ tới một khóa chính nhiều cột phải viết thế nào, và điều gì xảy ra nếu một trong hai cột là NULL?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Khóa ngoại InnoDB cần chỉ mục ở cả hai bên để kiểm tra nhanh, nên MySQL tự tạo chỉ mục cho cột con khi thiếu. Bỏ khóa ngoại không xóa chỉ mục đó [Đã chạy: MySQL 8.0.44]:

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
SHOW CREATE TABLE orders\G

*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `id` int NOT NULL,
  `customer_id` int NOT NULL,
  `total` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_orders_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Dòng KEY fk_orders_customer vẫn còn sau khi ràng buộc đã biến mất. Muốn dọn hẳn thì chạy thêm DROP INDEX.

• So sánh với lựa chọn khác. Hành động xóa lan (ON DELETE CASCADE) do chính InnoDB thực hiện, không đi qua trigger của bảng con. Theo tài liệu MySQL, các hành động lan truyền không kích hoạt trigger. Bài thử [Đã chạy: MySQL 8.0.44]:

CREATE TABLE audit (msg VARCHAR(50));
CREATE TRIGGER trg_orders_ad AFTER DELETE ON orders
  FOR EACH ROW INSERT INTO audit VALUES (CONCAT('order ', OLD.id, ' deleted'));

INSERT INTO orders VALUES (10, 1), (11, 1), (12, 2);
DELETE FROM orders WHERE id = 12;       -- xóa trực tiếp
DELETE FROM customers WHERE id = 1;     -- xóa lan sang đơn 10 và 11

SELECT COUNT(*) AS orders_left FROM orders;
SELECT msg FROM audit;

+-------------+
| orders_left |
+-------------+
|           0 |
+-------------+
+------------------+
| msg              |
+------------------+
| order 12 deleted |
+------------------+

Chỉ lần xóa trực tiếp đơn 12 để lại dấu vết trong bảng audit. Hai đơn bị xóa lan không để lại gì. Nếu bạn cần ghi nhật ký, đừng dựa vào trigger của bảng con.

• Hiểu lầm nâng cao. Nhiều người nghĩ tắt foreign_key_checks chỉ để tạm bỏ qua thứ tự bảng. Thật ra nó tắt kiểm tra cho mọi lệnh trong phiên, nên nên bật lại ngay sau khi nạp xong và chạy truy vấn tìm mồ côi.
• Giới hạn của bài. Bài này chưa nói đến khóa ngoại nhiều cột (bài Khóa phức hợp), ON UPDATE, khóa ngoại tự tham chiếu, và cách khóa ngoại ảnh hưởng tới khóa dòng khi nhiều giao dịch chạy cùng lúc.
• Đọc thêm. MySQL 8.0 Reference Manual, trang "FOREIGN KEY Constraints": https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Bài tiếp theo

Bài sau là khóa phức hợp trong MySQL: thứ tự cột quyết định chỉ mục có được dùng không, khóa ngoại nhiều cột và quy tắc NULL, và vì sao AUTO_INCREMENT không đếm theo nhóm trong InnoDB. Mini-dự án nối tiếp: bảng order_items của cửa hàng shop với khóa chính hai cột.

Lời giải và đáp án

Làm cùng mình, Bước 2. Hai chỗ trống điền như sau:

WHERE c.id IS NULL

ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)

Làm cùng mình, Bước 3. Lệnh ALTER TABLE thất bại với lỗi 1452 vì đơn 3 và 4 trỏ tới khách 7 và 8. Truy vấn LEFT JOIN liệt kê đúng hai đơn đó.

Làm cùng mình, Bước 4. Xóa khi đơn là dữ liệu rác, không có giá trị kế toán. Chuyển sang bảng chờ xử lý khi đơn có tiền và cần người đối soát. Tạo khách còn thiếu khi bạn biết chắc khách đó có thật nhưng bị xóa nhầm. Với bài này, ta chọn xóa rồi gắn khóa ngoại [Đã chạy: MySQL 8.0.44]:

DELETE o FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, DELETE_RULE, UPDATE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();

+--------------------+------------+-----------------------+-------------+-------------+
| CONSTRAINT_NAME    | TABLE_NAME | REFERENCED_TABLE_NAME | DELETE_RULE | UPDATE_RULE |
+--------------------+------------+-----------------------+-------------+-------------+
| fk_orders_customer | orders     | customers             | NO ACTION   | NO ACTION   |
+--------------------+------------+-----------------------+-------------+-------------+

Bài 1.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);
SHOW CREATE TABLE orders\G

*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `id` int NOT NULL,
  `customer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Tên ràng buộc là orders_ibfk_1, và chỉ mục tự thêm là KEY customer_id (customer_id).

Bài 2.

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
ORDER BY CONSTRAINT_NAME;

+--------------------+-------------+-----------------------+-------------+
| CONSTRAINT_NAME    | TABLE_NAME  | REFERENCED_TABLE_NAME | DELETE_RULE |
+--------------------+-------------+-----------------------+-------------+
| fk_items_order     | order_items | orders                | CASCADE     |
| fk_orders_customer | orders      | customers             | RESTRICT    |
+--------------------+-------------+-----------------------+-------------+

Bài 3.

SET foreign_key_checks = 0;
INSERT INTO orders VALUES (1, 1, 100000), (2, 2, 200000);
INSERT INTO customers VALUES (1, 'An'), (2, 'Binh');
SET foreign_key_checks = 1;

SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

+---------------+
| orphan_orders |
+---------------+
|             0 |
+---------------+

Truy vấn cuối không thể bỏ vì bật lại foreign_key_checks không kiểm tra lại dữ liệu. Ở đây kết quả là 0 vì hai khách được nạp sau đều khớp hai đơn đã nạp trước; nếu bạn nạp thiếu một khách, truy vấn sẽ tìm ra đúng đơn mồ côi.

Tự kiểm tra.

1. Vì MyISAM không có cơ chế lưu và thi hành khóa ngoại, MySQL chấp nhận cú pháp để tương thích rồi bỏ mệnh đề đó khi tạo bảng.
2. SHOW CREATE TABLE tên_bảng, kiểm tra có dòng CONSTRAINT ... FOREIGN KEY và ENGINE=InnoDB.
3. Lỗi 3780 vì INT UNSIGNED và INT không tương thích.
4. Còn dòng mồ côi trong bảng con. Tìm bằng LEFT JOIN bảng cha với điều kiện WHERE cha.id IS NULL.
5. Không bị chặn. Cổng chỉ kiểm tra khi cột khóa ngoại thay đổi, nên UPDATE cột khác vẫn qua dù dòng đã mồ côi từ trước.