Foreign Key với SET NULL trong SQL Server

Hướng dẫn dưới đây sẽ giải thích Foreign Key (khóa ngoại) với ràng buộc SET NULL ON DELETE trong SQL Server.

Khóa ngoại với SET NULL ON DELETE là gì?

Khóa ngoại ràng buộc SET NULL ON DELETE có nghĩa là khi một bản ghi trong bảng cha bị xóa, bản ghi tương ứng trong bảng con tại trường giá trị khóa ngoại sẽ được đặt thành NULL. Bản ghi trong bảng con sẽ không bị xóa khỏi SQL Server.

Khóa ngoại với ràng buộc đặt giá trị NULL khi xóa có thể tạo bằng lệnh CREATE TABLE hoặc ALTER TABLE.

Tạo khóa ngoại với ràng buộc SET NULL khi xóa bằng 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 SET NULL
 [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

Tham số

bang_con: Tên bảng con muốn tạo.

cot1, cot2: Các cột muốn tạo trong bảng. Mỗi cột có 1 kiểu dữ liệu, phải chỉ định chứa NULL hoặc NOT NULL, nếu không mặc định là NULL.

Kiểu dữ liệu trong SQL Server

fk_ten: Tên 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 đến khóa chính trong bang_me.

bang_me: Tên bảng cha chứa khóa chính dùng trong bang_con.

cot_me1, cot_me2, … cot_me_n: Các cột cấu thành khóa chính trong bang_me.

ON DELETE SET NULL: Dữ liệu trong bảng con sẽ được đặt thành NULL khi dữ liệu trong bảng cha bị xóa. Dữ liệu con không bị xóa.

ON UPDATE: Tùy chọn. Chỉ định hành động với dữ liệu con khi dữ liệu cha được cập nhật. Các tùy chọn: NO ACTION, CASCADE, SET NULL, SET DEFAULT.

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,
  soluong INT,
  luong_toithieu INT,
  luong_toida INT,
  CONSTRAINT fk_htk_id_sanpham
  FOREIGN KEY (id_sanpham)
  REFERENCES sanpham (id_sanpham)
  ON DELETE SET NULL
);

Trong ví dụ này, chúng ta tạo bảng cha sanpham với khóa chính gồm trường id_sanpham. Sau đó là bảng con 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 tên fk_htk_id_sanpham. Khóa ngoại thiết lập 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 SET NULL để báo cho SQL Server hiểu rằng cần đặt giá trị bản ghi tương ứng trong bảng con thành NULL khi dữ liệu trong bảng cha bị xóa. Trong ví dụ này, khi id_sanpham bị xóa từ bảng sanpham, bản ghi tương ứng trong bảng hangtonkho dùng id_sanpham sẽ được đặt thành NULL.

Lưu ý quan trọng: vì cột id_sanpham trong bảng hangtonkho sẽ được đặt thành NULL, cần đảm bảo cột này cho phép nhận giá trị NULL. Nếu đặt NOT NULL khi tạo, sẽ gặp lỗi sau:

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 SET NULL
);

Msg 1761, Level 16, State 0, Line 1
Cannot create the foreign key 'fk_htk_id_sanpham' with the SET NULL referential action, because one or more referencing columns are not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Đảm bảo định nghĩa cột id_sanpham trong bảng hangtonkho cho phép giá trị NULL như sau:

CREATE TABLE hangtonkho
( id_hangtonkho INT PRIMARY KEY,
  id_sanpham INT,
  soluong INT,
  luong_toithieu INT,
  luong_toida INT,
  CONSTRAINT fk_htk_id_sanpham
  FOREIGN KEY (id_sanpham)
  REFERENCES sanpham (id_sanpham)
  ON DELETE SET NULL
);

Command(s) completed successfully.

Tạo khóa ngoại với ràng buộc SET NULL khi xóa bằng ALTER TABLE