Xoá và cập nhật dữ liệu với foreign key

Ok, tụi mình đã biết foreign key (FOREIGN KEY) là gì, nó hoạt động ra sao, và cũng đã thử tạo bảng có foreign key rồi. Nhưng nếu đến lúc phải xoá dữ liệu hoặc sửa các bản ghi liên quan thì sao? Giờ mình sẽ cùng nhau tìm hiểu cách xoá và cập nhật dữ liệu khi có foreign key, và những mẹo nhỏ ở đây nhé.

Hãy tưởng tượng database giống như một toà nhà làm bằng thẻ bài vậy. Nếu bạn rút một thẻ ra, có thể làm sập cả toà nhà luôn. Đó là lúc foreign key xuất hiện để ngăn database của bạn bị "sập" khi xoá dữ liệu liên quan. Cùng xem nó hoạt động thế nào nha.

Chuyện gì xảy ra nếu thử xoá dữ liệu?

Khi trong bảng có foreign key, DBMS sẽ kiểm tra xem bản ghi đó có liên kết với bảng khác không. Nếu có, việc xoá bản ghi có thể gây ra lỗi toàn vẹn dữ liệu. Để tránh bất ngờ, bạn có thể đặt trước hành động cho foreign key. Các hành động này được cấu hình bằng tuỳ chọn ON DELETE.

Cấu hình hành vi với ON DELETE

Bạn có thể chọn một trong các quy tắc sau cho foreign key khi tạo nó:

1. ON DELETE CASCADE: Xoá bản ghi ở bảng cha sẽ tự động xoá tất cả bản ghi liên quan ở bảng con.
2. ON DELETE SET NULL: Thay vì xoá bản ghi liên quan, foreign key của chúng sẽ được set thành NULL.
3. ON DELETE SET DEFAULT: Giá trị foreign key sẽ được set về giá trị mặc định.
4. ON DELETE RESTRICT (mặc định): Không thể xoá bản ghi nếu có bản ghi liên quan, sẽ báo lỗi.
5. ON DELETE NO ACTION: Gần giống RESTRICT, nhưng kiểm tra toàn vẹn sẽ hoãn đến khi kết thúc transaction.

Ví dụ: Xoá cascade ON DELETE CASCADE

-- Bảng khách hàng
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Bảng đơn hàng
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Thêm dữ liệu
INSERT INTO customers (name) VALUES ('Ivan Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Xoá khách hàng
DELETE FROM customers WHERE customer_id = 1;

-- Kiểm tra bảng orders
SELECT * FROM orders; -- Không còn bản ghi nào, đã bị xoá cascade!

Khi mình xoá bản ghi trong bảng customers, PostgreSQL sẽ tự động xoá tất cả đơn hàng của khách đó trong bảng orders.

Ví dụ: Set foreign key thành NULL ON DELETE SET NULL

-- Bảng đơn hàng với quy tắc mới
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Thêm dữ liệu
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Xoá khách hàng
DELETE FROM customers WHERE customer_id = 1;

-- Kiểm tra bảng orders_with_null
SELECT * FROM orders_with_null;

Kết quả:

Dùng ON DELETE SET NULL giúp mình giữ lại đơn hàng, nhưng "cắt liên kết" với khách hàng đã bị xoá.

Cập nhật dữ liệu với foreign key

Ngoài xoá, việc cập nhật dữ liệu ở bảng cha cũng có thể ảnh hưởng đến bản ghi liên quan. Ví dụ, nếu khách hàng đổi customer_id thì sao? Đó là lúc tuỳ chọn ON UPDATE phát huy tác dụng.

Cấu hình hành vi với ON UPDATE

Xử lý thay đổi ở bảng cha bằng các chiến lược sau:

1. ON UPDATE CASCADE: Thay đổi giá trị foreign key ở bảng cha sẽ tự động cập nhật ở tất cả bản ghi liên quan.
2. ON UPDATE SET NULL: Giá trị foreign key ở bảng con sẽ được set thành NULL.
3. ON UPDATE SET DEFAULT: Set về giá trị mặc định.
4. ON UPDATE RESTRICT: Không cho phép thay đổi foreign key nếu có bản ghi liên quan.
5. ON UPDATE NO ACTION: Kiểm tra sẽ hoãn đến cuối transaction.

Ví dụ: Cập nhật cascade ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Thêm dữ liệu
INSERT INTO customers_with_cascade (name) VALUES ('Ivan Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Đổi customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Kiểm tra bảng orders_with_cascade
SELECT * FROM orders_with_cascade;

Kết quả:

Khi đổi customer_id, PostgreSQL sẽ tự động cập nhật nó trong bảng orders_with_cascade.

Luyện tập: Bảng enrollments

Nhớ ví dụ về sinh viên students và khoá học courses chứ? Mình sẽ làm việc với bảng ghi danh khoá học enrollments và cấu hình quản lý xoá dữ liệu nhé.

-- Bảng sinh viên
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Bảng khoá học
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Bảng trung gian ghi danh
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Thêm dữ liệu
INSERT INTO students (name) VALUES ('Aleksei Petrov');
INSERT INTO courses (title) VALUES ('Toán học');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Xoá sinh viên
DELETE FROM students WHERE student_id = 1;

-- Kiểm tra bảng enrollments
SELECT * FROM enrollments; -- Trống! Bản ghi đã bị xoá tự động.

Lỗi phổ biến và cách tránh

Một lỗi hay gặp là cố xoá bản ghi ở bảng cha mà không cấu hình đúng hành vi foreign key. Ví dụ, nếu bạn không chỉ định gì cho ON DELETE, mặc định sẽ là RESTRICT và sẽ gây lỗi.

Cũng cần nhớ, dùng cascade (CASCADE) quá nhiều có thể gây hậu quả bất ngờ. Bạn có thể vô tình xoá nhiều dữ liệu hơn dự tính.

Để tránh mấy lỗi này, hãy làm theo các tips sau:

• Luôn suy nghĩ kỹ về hành vi ON DELETE và ON UPDATE dựa trên logic ứng dụng của bạn.
• Với thao tác quan trọng, hãy thêm truy vấn kiểm tra trước khi xoá hoặc sửa dữ liệu.
• Dùng transaction để có thể rollback nếu có lỗi.