Foreign Key với Cascade Delete trong SQL Server

Foreign Key với Cascade Delete trong SQL Server được sử dụng khá phổ biến. Dưới đây là những điều bạn cần biết về Foreign Key với Cascade Delete của SQL Server.

Cascade SQL Server là gì? On delete cascade trong SQL Server được dùng như thế nào? Bài viết sẽ hướng dẫn cách sử dụng Foreign Key với Cascade Delete trong SQL Server cùng cú pháp và các ví dụ. Hãy cùng Quantrimang.com tìm hiểu nhé!

Foreign key được dùng để thiết lập ràng buộc tham chiếu giữa bảng con (trong cột được xác định là khóa ngoại) và bảng cha (trong khóa ngoại của bảng con trở thành khóa chính). Ví dụ nếu có bảng ORDER trong khóa ngoại được xác định dưới dạng TRANSACTION_ID. Khóa ngoại này sẽ tham chiếu tới cột TRANSACTION_ID của bảng TRANSACTIONS. Trong bảng TRANSACTIONS, TRANSACTIONS_ID sẽ là khóa chính. Bảng cha ở đây là bảng TRANSACTIONS, còn bảng con là bảng ORDERS.

Quy tắc CASCADE của khóa ngoại cho biết thời điểm mục bất kỳ được xóa khỏi bảng cha, sau đó tất cả các hàng phụ thuộc trong bảng con cũng sẽ bị khóa. Ví dụ, nếu có một mục trong bảng TRANSACTION với TRANSACTION_ID là ‘A2234’ (khóa cơ bản). Nếu mục này bị xóa khỏi bảng TRANSACTIONS, sau đó toàn bộ hàng trong bảng ORDERS có khóa ngoại TRANSACTION_ID là ‘A2234’ sẽ bị xóa. Chúng ta có thể thiết lập quy tắc CASCADE như bên dưới.

CREATE TABLE ORDERS
(ORDER_ID CHAR(15) NOT NULL,
ORDER_DATE DATE,
ORDER_TOTAL DECIMAL(9,2),
TRANSACTION_ID CHAR(15),
PRIMARY KEY(ORDER_ID),
FOREIGN KEY(TRANSACTION_ID) REFERENCES TRANSACTIONS
ON DELETE CASCADE)
IN DB4ES01;

Từ khóa REFERENCES được theo sau bởi bảng cha và để đặt quy tắc CASCADE, chúng ta sẽ dùng từ khóa ON DELETE CASCADE.

Khóa ngoại với Cascade DELETE trong SQL Server là gì?

Hiểu đơn giản, khóa ngoại với Cascade Delete nghĩa là nếu một bản ghi trong bảng mẹ bị xóa thì bản ghi tương ứng trong bảng con cũng sẽ tự động bị xóa.

Khóa ngoại với ràng buộc tự động xóa được tạo bằng cách dùng lệnh CREATE TABLE hoặc ALTER TABLE.

Tạo khóa ngoại với ràng buộc tự động xóa bằng lệnh CREATE TABLE

Cú pháp

CREATE TABLE bang_con(  cot1 kieudulieu [ NULL | NOT NULL ], cot2 kieudulieu [ NULL | NOT NULL ], … CONSTRAINT fk_ten  FOREIGN KEY (cot_con1, cot_con2, … cot_con_n)  REFERENCES bang_me (cot_me1, cot_me2, … cot_me_n)   ON DELETE CASCADE   [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]);

bang_con

Tên của bảng con muốn tạo.

cot1, cot2

Cột muốn tạo trong bảng. Mỗi cột có 1 loại dữ liệu, phải được chỉ định là chứa giá trị NULL hay NOT NULL, nếu không sẽ mặc định là NULL.

fk_ten

Tên của ràng buộc khóa ngoại muốn tạo.

cot_con1, cot_con2, … cot_con_n

Cột trong bang_con muốn tham chiếu tới khóa chính trong bang_me.

bang_me

Tên của bảng mẹ chứa khóa chính được dùng trong bang_con.

cot_me1, cot_me2, … cot_me_n

Cột tạo nên khóa chính trong bang_me. Khóa ngoại sẽ tạo ràng buộc giữa dữ liệu và các cột cot_con1, cot_con2, … cot_con_n trong bang_con.

ON DELETE CASCADE

Chỉ định dữ liệu con sẽ bị xóa khi dữ liệu mẹ bị xóa.

ON UPDATE

Tùy chọn. Cho biết sẽ làm gì với dữ liệu con khi dữ liệu mẹ được cập nhật. Có các lựa chọn NO ACTION, CASCADE, SET NULL và SET DEFAULT.

NO ACTION

Dùng với ON DELETE hoặc ON UPDATE, nghĩa là không làm gì với dữ liệu con khi dữ liệu mẹ bị xóa hoặc cập nhật.

CASCADE

Dùng với ON DELETE hoặc ON UPDATE, nghĩa là dữ liệu con bị xóa hoặc cập nhật khi dữ liệu mẹ bị xóa hoặc cập nhật.

SET NULL

Dùng với ON DELETE hoặc ON UPDATE, nghĩa là dữ liệu con được đặt là NULL khi dữ liệu mẹ bị xóa hoặc cập nhật.

SET DEFAULT

Dùng với ON DELETE hoặc ON UPDATE, nghĩa là dữ liệu con được đặt thành giá trị mặc định khi dữ liệu mẹ bị xóa hoặc cập nhật.

Ví dụ

CREATE TABLE sanpham( id_sanpham INT PRIMARY KEY,  ten_sanpham VARCHAR(50) NOT NULL,  phan_loai VARCHAR(25));CREATE TABLE hangtonkho( id_hangtonkho INT PRIMARY KEY,   id_sanpham INT NOT NULL,  soluong INT,  luong_toithieu INT,  luong_toida INT,  CONSTRAINT fk_htk_id_sanpham   FOREIGN KEY (id_sanpham)   REFERENCES sanpham (id_sanpham)    ON DELETE CASCADE );

Ở ví dụ này, chúng ta tạo ra bảng mẹ là sanpham với khóa chính gồm trường thông tin là id_sanpham. Sau đó là bảng con có tên hangtonkho với khóa ngoại có ràng buộc xóa. Lệnh CREATE TABLE tạo khóa ngoại trên bảng hangtonkho có tên fk_htk_id_sanpham. Khóa ngoại hình thành mối quan hệ giữa cột id_sanpham trong bảng hangtonkho và id_sanpham trong bảng sanpham.

Khóa ngoại này được chỉ định ON DELETE CASCADE nói cho SQL Server biết rằng phải xóa bản ghi tương ứng trong bảng con khi dữ liệu ở bảng mẹ bị xóa. Ở ví dụ này, giá trị id_sanpham bị xóa khỏi bảng sanpham thì bản ghi tương ứng trong bảng con hangtonkho dùng id_sanpham này cũng sẽ bị xóa.

Tạo khóa ngoại với ràng buộc xóa bằng lệnh ALTER TABLE

Cú pháp

ALTER TABLE bang_conADD CONSTRAINT fk_ten FOREIGN KEY (cot_con1, cot_con2, … cot_con_n)  REFERENCES bang_me (cot_me1, cot_me2, … cot_me_n)ON DELETE CASCADE;

bang_con

Tên của bảng con muốn tạo.

fk_ten

Tên của ràng buộc khóa ngoại muốn tạo.

cot_con1, cot_con2, … cot_con_n

Cột trong bang_con muốn tham chiếu tới khóa chính trong bang_me.

bang_me

Tên của bảng mẹ chứa khóa chính được dùng trong bang_con.

cot_me1, cot_me2, … cot_me_n

Cột tạo nên khóa chính trong bang_me. Khóa ngoại sẽ tạo ràng buộc giữa dữ liệu và các cột cot_con1, cot_con2, … cot_con_n trong bang_con.

ON DELETE CASCADE

Giới hạn ON DELETE CASCADE được dùng trong SQL Server để tự động xóa các hàng từ bảng con, khi hàng từ bảng cha được xóa. Ví dụ khi học sinh đăng ký một nền tảng học online, sau đó tất cả thông tin chi tiết của học sinh đó đều sẽ được ghi lại cùng với số/ID riêng. Toàn bộ khóa học trong các nền tảng học online đều có code, tiêu đề và tên riêng. Học sinh/sinh viên có thể tham gia khóa học bất kỳ như ý muốn.

Không có quy định học sinh phải tham gia toàn bộ khóa học hay phải vào khóa học nào đó trong cùng một ngày. Một học sinh có thể tham gia vào một hoặc nhiều khóa học hơn. Giả sử bạn xóa một hàng từ bảng Student, đồng thời muốn xóa tất cả hàng ở bảng Enroll mà tham chiếu tới hàng trong bảng Student. Lúc này, bạn cần tới ON DELETE CASCADE. Dưới đây là một ví dụ minh họa cách dùng ON DELETE CASCADE trong SQL Server.

Ví dụ

ALTER TABLE hangtonkho ADD CONSTRAINT fk_htk_id_sanpham FOREIGN KEY (id_sanpham)  REFERENCES sanpham (id_sanpham) ON DELETE CASCADE;

Trong ví dụ về khóa ngoại này, bảng con hangtonkho được tạo với khóa ngoại có tên fk_htk_id_sanpham, tham chiếu tới bảng mẹ sanpham dựa trên id_sanpham.

Vì chỉ định ON DELETE CASCADE nên nếu giá trị id_sanpham bị xóa khỏi bảng mẹ thì bản ghi tuong ứng trong bảng con cũng sẽ bị xóa.

ON DELETE CASCADE và ON DELETE SET NULL là hai tùy chọn quan trọng trong ràng buộc khóa ngoại SQL xác định cách cơ sở dữ liệu xử lý các bản ghi liên quan trong bảng con khi một bản ghi trong bảng cha bị xóa. Các tùy chọn này rất quan trọng để duy trì tính toàn vẹn tham chiếu và đảm bảo cấu trúc cơ sở dữ liệu nhất quán.

Bảng sau đây liệt kê tất cả các điểm khác biệt chính giữa ON DELETE CASCADE và ON DELETE SET NULL:

Các phương pháp thay thế

Nếu việc xóa theo tầng có vẻ quá phức tạp đối với trường hợp sử dụng của bạn, SQL Server cung cấp các tùy chọn khác:

• ON DELETE SET NULL: Đặt giá trị khóa ngoại thành NULL khi bản ghi cha bị xóa
• ON DELETE SET DEFAULT: Đặt giá trị khóa ngoại thành giá trị mặc định
• ON DELETE NO ACTION: Thực thi rằng nếu việc xóa dẫn đến các bản ghi mồ côi trong bảng con, thao tác xóa sẽ bị cấm và lỗi sẽ được phát sinh. Đây là hành vi mặc định. Nói cách khác, nếu chúng ta không chỉ định bất kỳ hành động ON DELETE nào, thì đây là hành động được sử dụng. Tùy chọn này cho phép chúng ta xóa thủ công các bản ghi con nếu cần. Trong trường hợp này, chúng ta cần viết mã rõ ràng để xử lý việc dọn dẹp bản ghi liên quan.

Lựa chọn tùy thuộc vào yêu cầu kinh doanh của bạn và cách bạn muốn xử lý các mối quan hệ dữ liệu khi các bản ghi cha bị xóa.