DROP TABLE và ALTER TABLE trong SQLite: thay đổi cấu trúc bảng

Cấu trúc bảng sẽ thay đổi, và SQLite cho phép (phần lớn)

Khi bảng đã tồn tại, sớm muộn bạn cũng muốn đổi tên bảng, thêm cột, xóa cột hoặc sắp xếp lại toàn bộ. SQLite hỗ trợ trực tiếp những trường hợp phổ biến bằng DROP TABLE và ALTER TABLE, và có sẵn một cách làm được ghi trong tài liệu cho mọi trường hợp còn lại.

Điểm cần lưu ý: ALTER TABLE của SQLite hạn chế hơn nhiều so với Postgres hay MySQL. Biết nó làm được gì, không làm được gì, cùng cách tạo lại bảng cho những việc nó không làm được, là phần lớn kỹ năng ở đây.

DROP TABLE xóa bảng và mọi thứ gắn với nó

DROP TABLE xóa bảng, các hàng của bảng, các index và mọi trigger được định nghĩa trên bảng. Không có cách hoàn tác:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporary'), ('also temporary');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

Bảng đã biến mất. Truy vấn bảng này lúc này sẽ báo lỗi no such table: scratch.

Nếu bạn không chắc bảng có tồn tại hay không (thường gặp trong script khởi tạo), hãy dùng IF EXISTS để câu lệnh lặng lẽ bỏ qua khi bảng không có:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'both drops succeeded' AS result;

Không có IF EXISTS, lệnh xóa thứ hai sẽ báo lỗi. Có nó, cả hai đều chạy suôn sẻ.

Khóa ngoại có thể chặn lệnh DROP

Nếu chế độ kiểm tra khóa ngoại (foreign key) đang bật (PRAGMA foreign_keys = ON;) và có bảng khác tham chiếu đến bảng bạn định xóa, lệnh xóa sẽ thất bại:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

Bạn có vài lựa chọn: xóa bảng tham chiếu trước, xóa các hàng đang tham chiếu, hoặc khai báo khóa ngoại với ON DELETE CASCADE ngay khi tạo bảng. SQLite sẽ không âm thầm phá vỡ tính toàn vẹn tham chiếu giúp bạn.

ALTER TABLE: bốn việc nó làm được

ALTER TABLE của SQLite hỗ trợ đúng bốn thao tác:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT
);

-- 1. Rename the table
ALTER TABLE posts RENAME TO articles;

-- 2. Rename a column
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Add a column
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Drop a column (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Mỗi thao tác chạy bằng một câu lệnh duy nhất. Hai thao tác đầu gần như không tốn gì, vì chúng chỉ cập nhật cấu trúc bảng. ADD COLUMN cũng nhanh: SQLite không ghi lại bảng mà chỉ lưu định nghĩa của cột mới. DROP COLUMN nặng hơn, vì SQLite phải ghi lại từng hàng để thực sự xóa dữ liệu của cột.

ADD COLUMN với giá trị mặc định

Cột mới thêm vào bảng đã có dữ liệu sẽ mang giá trị NULL ở mọi hàng, trừ khi bạn đặt giá trị mặc định cho nó:

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

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Cả hai hàng hiện có đều được điền 'active'. Giá trị mặc định phải là hằng số: SQLite không cho dùng CURRENT_TIMESTAMP hay bất kỳ biểu thức không cố định nào làm giá trị mặc định trong ADD COLUMN, vì nó cần một giá trị áp được cho mọi hàng hiện có mà không phải tính lại theo từng hàng.

Nếu bạn cần NOT NULL mà không có giá trị mặc định, bạn phải thêm cột cho phép null, điền dữ liệu bằng UPDATE, rồi tạo lại bảng để thêm ràng buộc. Điều này dẫn ta đến các giới hạn.

Những việc ALTER TABLE không làm được

Những thao tác chạy được trong Postgres hay MySQL nhưng không có trong SQLite:

• Đổi kiểu của một cột (ALTER COLUMN ... TYPE ...).
• Sửa giá trị mặc định của cột tại chỗ.
• Thêm hoặc bỏ NOT NULL, CHECK, UNIQUE hay PRIMARY KEY trên một cột đã có.
• Thêm khóa ngoại cho một cột đã có.
• Sắp xếp lại thứ tự các cột.

Thử bất kỳ việc nào ở trên đều cho ra lỗi cú pháp. SQLite hoàn toàn không có mệnh đề ALTER COLUMN. Câu trả lời chính thức cho tất cả đều giống nhau: tạo lại bảng.

Cách tạo lại bảng

Khi ALTER TABLE không làm được điều bạn cần, bạn tạo một bảng mới với cấu trúc mong muốn, chép dữ liệu sang, xóa bảng cũ và đổi tên bảng mới thành tên cũ. Đặt tất cả trong một transaction để hoặc thành công trọn vẹn, hoặc không thay đổi gì:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

Giờ users.age là số nguyên có ràng buộc kiểm tra, còn email là NOT NULL. Dữ liệu được mang theo đầy đủ.

Vài điều cần nhớ khi làm việc này trên dữ liệu thật:

• Tắt khóa ngoại trong lúc thực hiện. Nếu có bảng khác tham chiếu đến bảng của bạn, chạy PRAGMA foreign_keys = OFF; trước transaction và PRAGMA foreign_keys = ON; sau đó. Nếu không, DROP TABLE sẽ thất bại. Pragma này không đổi được bên trong transaction, nên hãy đặt nó ở ngoài.
• Tạo lại index và trigger. Xóa bảng cũ cũng xóa luôn index và trigger của nó. Hãy thêm lại chúng trên bảng mới sau khi đổi tên.
• Kiểm tra view. Các view tham chiếu đến bảng vẫn trỏ đến tên cũ trong câu SQL đã lưu. Tạo lại những view phụ thuộc vào các cột đã thay đổi.

Cách tạo lại bảng hơi dài dòng nhưng đáng tin cậy. Đây chính là điều các công cụ migration như Alembic và Rails làm ngầm bên dưới khi làm việc với SQLite.

Xóa nhiều bảng

Không có câu lệnh nào xóa nhiều bảng cùng lúc: bạn chạy DROP TABLE cho từng bảng. Đặt trong một transaction nếu muốn gom chúng lại:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Đặt chúng trong một transaction nghĩa là hoặc cả ba lệnh xóa đều thành công, hoặc không lệnh nào thành công. Điều này hữu ích khi dọn các bảng liên quan với nhau mà có thể thất bại giữa chừng vì khóa ngoại.

Những điều cần nhớ

• DROP TABLE xóa bảng cùng các index và trigger của nó. Dùng IF EXISTS để script chạy lại nhiều lần vẫn an toàn.
• ALTER TABLE chỉ làm được bốn việc: đổi tên bảng, đổi tên cột, thêm cột, xóa cột.
• Với mọi việc khác (đổi kiểu, thêm ràng buộc, thêm khóa ngoại cho cột đã có), hãy tạo lại bảng bên trong một transaction.
• Khi tạo lại bảng, để ý khóa ngoại, index, trigger và view. Chúng không tự đi theo dữ liệu.

Tiếp theo: thêm dữ liệu vào bảng

Bạn đã dành cả một chương cho bảng và các ràng buộc định hình chúng. Đến lúc điền dữ liệu vào: chương tiếp theo bắt đầu với INSERT, gồm cách thêm nhiều hàng một lúc, giá trị mặc định, và cách SQLite xử lý các lệnh thêm dữ liệu vi phạm ràng buộc.