Thêm/đổi ràng buộc và định nghĩa lại bảng

Dữ liệu sẽ dùng — legacy_user

Các bảng trong sản xuất thường hóa ra cần các ràng buộc không có trong thiết kế ban đầu.

Những thứ như "gắn một giá trị mặc định cho cột này", "làm email không trùng (UNIQUE)", hoặc "đánh dấu trường bắt buộc này là NOT NULL".

Bài viết này đề cập việc thêm cột và bổ sung giá trị mặc định, và quy trình dựng lại bảng để thêm ràng buộc vào một bảng đã tồn tại.

Trước khi vào bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của bảng legacy_user.

① Chạy PRAGMA table_info(legacy_user); để kiểm tra tên cột, kiểu, khóa chính, NOT NULL, và giá trị mặc định.

② Chạy SELECT * FROM legacy_user; để xem trước mọi dòng.

ALTER TABLE ADD COLUMN — bổ sung cột và giá trị mặc định

Để thêm một cột mới vào bảng đã tồn tại, dùng ALTER TABLE table ADD COLUMN col_name type constraint.

Gắn DEFAULT value đặt giá trị mặc định được dùng khi cột bị bỏ qua, và vào lúc ALTER giá trị này cũng lấp đầy mọi dòng hiện có.

ADD COLUMN thậm chí chấp nhận NOT NULL khi kết hợp với DEFAULT (các dòng hiện có được lấp bằng mặc định, nên không vi phạm NOT NULL).

Mặt khác, ADD COLUMN không thể trực tiếp gắn UNIQUE hoặc một NOT NULL không kèm mặc định.

Những thứ đó cần một "dựng lại bảng".

-- Thêm một cột với DEFAULT (dùng bảng tạm để xem hành vi)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- Thêm created_on với một DEFAULT → các dòng hiện có cũng nhận giá trị
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL cũng ổn khi kết hợp với DEFAULT
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

Hãy hình dung yêu cầu: "thêm một cột status vào bảng user và coi mọi user hiện có là active." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Thêm một cột TEXT tên status vào legacy_user với giá trị mặc định là 'active'.

② Rồi chạy SELECT * FROM legacy_user; và xác nhận cột status ở 2 dòng hiện có được lấp bằng giá trị mặc định.

Thêm ràng buộc sau khi tạo nghĩa là dựng lại bảng

Trong MySQL bạn có thể gắn ràng buộc trực tiếp vào bảng đã tồn tại, như ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);.

Trong console của khóa học này, bổ sung UNIQUE hoặc NOT NULL vào bảng đã tồn tại đi qua quy trình dựng lại bảng: "tạo một bảng mới với các ràng buộc bạn muốn, sao chép dữ liệu hiện có sang, và thay thế bảng cũ".

MySQL / PostgreSQL cũng thực hiện mẫu dựng lại này nội bộ cho một số thao tác (như đổi kiểu cột), tùy theo công cụ.

Quy trình theo hình dưới: tắt kiểm tra khóa ngoại, rồi CREATE bảng mới → sao chép mọi dòng → DROP bảng cũ → RENAME bảng mới → khôi phục kiểm tra.

Về ADD CONSTRAINT / AUTO_INCREMENT / comment cột của MySQL

Cú pháp ALTER TABLE ... ADD CONSTRAINT, AUTO_INCREMENT, và comment cột (COMMENT '...') trình bày dưới đây là cú pháp MySQL.

Console trên trình duyệt của khóa học này không thể chạy chúng, nên hai khối code này chỉ đọc (đừng cố thực thi).

Trong console của khóa học này, hãy bổ sung ràng buộc qua dựng lại bảng ở phần dưới, và dùng INTEGER PRIMARY KEY để đánh số tự động.

-- Cú pháp MySQL (đừng chạy trong console của khóa học — chỉ đọc)

-- MySQL: gắn một ràng buộc UNIQUE trực tiếp vào bảng đã tồn tại
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL: đổi sang NOT NULL qua MODIFY
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL: đánh số tự động là thuộc tính cột AUTO_INCREMENT; mô tả là comment cột
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- Trong console của khóa học này, đạt tương đương AUTO_INCREMENT qua INTEGER PRIMARY KEY
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Khung dựng lại bảng (dùng bảng tạm rebuild_demo để xem hành vi)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① Tắt kiểm tra khóa ngoại
PRAGMA foreign_keys=OFF;

-- ② Bảng mới với ràng buộc mong muốn (email NOT NULL UNIQUE)
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ Sao chép dữ liệu hiện có sang
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ Xóa bảng cũ, ⑤ đổi tên bảng mới thành tên cũ
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ Khôi phục kiểm tra khóa ngoại
PRAGMA foreign_keys=ON;

-- Xác nhận ràng buộc đã có (email trùng giờ sẽ báo lỗi)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

Hãy hình dung yêu cầu: "đổi cột email trên legacy_user thành bắt buộc và không trùng (NOT NULL và UNIQUE)." Chạy quy trình dựng lại bảng chính của bài viết, từng bước, đúng như hình ở trên.

① Tắt kiểm tra khóa ngoại (PRAGMA foreign_keys=OFF;).

② CREATE một bảng mới tên legacy_user_new với id (khóa chính INTEGER), name (TEXT), và email (TEXT, NOT NULL, UNIQUE).

③ Sao chép mọi dòng từ bảng hiện có với INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;.

④ DROP legacy_user cũ và RENAME legacy_user_new thành legacy_user.

⑤ Khôi phục kiểm tra khóa ngoại thành ON.

⑥ Cuối cùng, chạy SELECT sql FROM sqlite_master WHERE name='legacy_user'; và xác nhận schema mới chứa UNIQUE / NOT NULL.

Tái hiện cùng quy trình dựng lại trên một bảng tạm và trải nghiệm ràng buộc UNIQUE của schema mới thực sự chặn một dòng trùng. Bài tập này tự chứa và dùng bảng riêng email_check.

① DROP TABLE IF EXISTS email_check; rồi CREATE nó với id (khóa chính INTEGER) và email (TEXT, NOT NULL, UNIQUE).

② INSERT một dòng (1, 'a@example.com') vào email_check.

③ Rồi INSERT một dòng nữa (2, 'a@example.com') mà giá trị email đã tồn tại. Nó vi phạm ràng buộc UNIQUE, nên lỗi là kết quả đúng cho INSERT thứ hai.