Khóa ngoại trong SQLite: REFERENCES, ON DELETE và PRAGMA

Khóa ngoại là con trỏ giữa các bảng

Khóa ngoại (foreign key) là một cột trong một bảng mà giá trị của nó phải khớp với một hàng trong bảng khác. Đó là cách cơ sở dữ liệu quan hệ nói "hàng posts này thuộc về hàng authors kia" mà không phải chép tên và email của tác giả vào mọi bài viết.

Đây là ví dụ nhỏ nhất có thể: một bảng cha và một bảng con được liên kết bằng khóa ngoại:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('Notes', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) là toàn bộ khai báo khóa ngoại. Nó nói rằng: cột này chứa một id từ bảng authors. Giờ cơ sở dữ liệu biết hai bảng có liên quan với nhau, và nếu chế độ kiểm tra đang bật, nó sẽ từ chối những lệnh thêm dữ liệu trỏ đến tác giả không tồn tại.

Khóa ngoại mặc định bị tắt

Đây là điều quan trọng nhất về khóa ngoại trong SQLite, và ai cũng bất ngờ: SQLite đọc hiểu mệnh đề REFERENCES nhưng không kiểm tra chúng trừ khi bạn yêu cầu. Lý do là để tương thích với các phiên bản cũ: những cơ sở dữ liệu đời đầu được tạo ra trước khi tính năng này tồn tại.

Xem điều gì xảy ra khi không bật kiểm tra:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- author 999 does not exist, but SQLite happily accepts this:
INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

SELECT * FROM posts;

Hàng mồ côi đã được thêm vào ngay. Để có được sự bảo vệ bạn thực sự cần, chạy PRAGMA foreign_keys = ON; ở đầu mỗi kết nối:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

Giờ lệnh thêm thất bại với lỗi FOREIGN KEY constraint failed. Pragma này áp dụng theo từng kết nối, không theo cơ sở dữ liệu: thiết lập không được lưu trong tệp. Mọi ứng dụng, mọi phiên CLI, mọi fixture kiểm thử đều phải đặt nó. Hầu hết code production chạy PRAGMA foreign_keys = ON; ngay sau khi mở kết nối.

Mệnh đề REFERENCES thực sự đòi hỏi gì

Cột bạn tham chiếu phải là PRIMARY KEY hoặc có ràng buộc UNIQUE. Nhờ vậy SQLite mới đảm bảo được mỗi lần tra cứu đều rõ ràng. Kiểu dữ liệu cũng nên tương thích: SQLite khá dễ dãi về kiểu, nhưng trộn lẫn chúng là tự chuốc lấy bất ngờ.

Bạn có thể viết khóa ngoại theo hai cách. Viết ngay trên cột:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Hoặc viết thành ràng buộc riêng ở cấp bảng, cách bắt buộc khi khóa ngoại gồm nhiều cột:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Cả hai cách đều tạo ra ràng buộc giống hệt nhau. Dùng cách nào dễ đọc hơn với bảng đang viết.

ON DELETE: chuyện gì xảy ra với hàng con

Khi bạn xóa một hàng cha, SQLite phải quyết định làm gì với các hàng con đang trỏ đến nó. Bạn chọn quy tắc bằng ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Xóa Ada cũng xóa luôn cả hai bài viết của cô ấy. Các lựa chọn gồm:

• CASCADE: xóa luôn các hàng con. Phù hợp với dữ liệu "thuộc sở hữu" như bài viết của một tác giả hay các mặt hàng trong một đơn hàng.
• SET NULL: gán null cho cột khóa ngoại. Phù hợp khi hàng con vẫn nên tồn tại khi không còn hàng cha (ví dụ bình luận của một người dùng đã bị xóa trở thành ẩn danh).
• SET DEFAULT: gán cho cột khóa ngoại giá trị mặc định đã khai báo.
• RESTRICT: chặn lệnh xóa nếu còn hàng con. Thất bại ngay khi câu lệnh chạy.
• NO ACTION: giá trị mặc định. Trong hầu hết trường hợp hoạt động giống RESTRICT (nó hoãn việc kiểm tra đến lúc commit, nhưng kết quả như nhau: bạn không thể để lại hàng con bị treo).

ON UPDATE hoạt động tương tự khi khóa của hàng cha thay đổi, dù việc cập nhật khóa chính rất hiếm gặp.

Foreign key constraint failed: nghĩa là gì

Bạn sẽ gặp lỗi này trong hai tình huống. Thứ nhất, thêm hoặc cập nhật một hàng con với giá trị không có hàng cha tương ứng:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Hoặc tác giả 999 không tồn tại, hoặc bạn đã nhầm kiểu của cột. Hãy thêm hàng cha trước, hoặc sửa lại giá trị.

Thứ hai, xóa (hoặc cập nhật) một hàng cha vẫn còn hàng con, khi khóa ngoại dùng RESTRICT hoặc NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Hãy xóa các hàng con trước, hoặc đổi khóa ngoại sang ON DELETE CASCADE/SET NULL nếu bạn thực sự muốn xóa dây chuyền.

Còn một lỗi họ hàng ít gặp hơn: FOREIGN KEY mismatch. Lỗi này xuất hiện khi cột được tham chiếu không phải khóa chính hay unique, hoặc số lượng cột không khớp. Đây là lỗi cấu trúc bảng, không phải lỗi dữ liệu.

Thêm khóa ngoại cho bảng đã có

ALTER TABLE của SQLite khá hạn chế: bạn có thể thêm một cột có khóa ngoại, nhưng không thể gắn khóa ngoại vào một cột đã tồn tại. Cách làm chuẩn là tạo lại bảng rồi đổi tên:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('World', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Các bước: tắt kiểm tra, tạo bảng mới với ràng buộc mong muốn, chép dữ liệu, xóa bảng cũ, đổi tên. BEGIN/COMMIT giúp toàn bộ diễn ra trọn vẹn. Bật lại kiểm tra ở cuối và SQLite sẽ áp dụng ràng buộc mới cho các thao tác sau đó; nhưng nếu có dữ liệu không hợp lệ, transaction đã commit rồi, nên hãy kiểm tra trước nếu bạn lo ngại.

Chạy PRAGMA foreign_key_check; sau khi chuyển đổi để xác nhận không còn hàng mồ côi nào.

Một cấu trúc thực tế

Ghép tất cả lại: cấu trúc của một blog nhỏ với bảng cha, bảng con và một bảng trung gian cho quan hệ nhiều-nhiều giữa bài viết và thẻ:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Ba điều cần để ý. author_id là NOT NULL: mọi bài viết đều phải có tác giả. Khóa ngoại posts → authors dùng cascade, nên xóa một tác giả sẽ xóa sạch bài viết của họ. Bảng trung gian post_tags dùng cascade ở cả hai phía, nên xóa một bài viết hay một thẻ đều tự động dọn các hàng liên kết.

Thói quen giúp bạn đỡ khổ về sau

• Đặt PRAGMA foreign_keys = ON; trên mọi kết nối. Đưa nó vào quy trình mở cơ sở dữ liệu của ứng dụng, đừng trông vào trí nhớ.
• Thêm index trên cột khóa ngoại. SQLite tự đánh index cho khóa của bảng cha nhưng không làm vậy cho bảng con, và ON DELETE CASCADE phải tra cứu bảng con mỗi lần hàng cha bị xóa.
• Chọn ON DELETE có chủ đích. Giá trị mặc định (NO ACTION) an toàn nhưng nghĩa là bạn sẽ gặp lỗi "constraint failed" mỗi lần dọn dữ liệu. Hãy quyết định điều nên xảy ra và khai báo nó.
• Chạy PRAGMA foreign_key_check; sau khi chuyển đổi cấu trúc hoặc nhập dữ liệu hàng loạt để phát hiện hàng mồ côi trước khi chúng thành bug.

Tiếp theo: INNER JOIN

Khóa ngoại mô tả mối quan hệ; join là cách bạn thực sự truy vấn qua mối quan hệ đó. Trang tiếp theo nói về INNER JOIN: kết hợp các hàng từ những bảng liên quan và lấy về những cột bạn cần từ mỗi bảng.