Foreign Key với Set Null trong SQL Server

Bài hướng dẫn dưới đây sẽ giải thích về khóa ngoại Foreign Key với ràng buộc Set Null On Delete trong SQL Server.

Khóa ngoại với Set NULL on Delete trong SQL Server là gì?

Khóa ngoại có ràng buộc Set Null on Delete nghĩa là khi một bản ghi trong bảng mẹ bị xóa thì bản ghi tương ứng ở bảng con ở trường giá trị khóa ngoại sẽ được đặt giá trị NULL. Bảng ghi trong bảng con sẽ không bị xóa khỏi SQL Server.

Khóa ngoại với điều kiện đặt giá trị NULL khi xóa có thể được tạo bằng lệnh CREATE TABLE hoặc ALTER TABLE.

Tạo khóa ngoại với ràng buộc đặt giá trị NULL khi 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 SET NULL 
  [ 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.

Các kiểu dữ liệu trong SQL Server

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

Dữ liệu ở bảng con sẽ được đặt thành NULL khi dữ liệu ở bảng mẹ bị xóa. Dữ liệu con không 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, 
  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
);

Ở 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 SET NULL để nói cho SQL Server biết rằng cần đặt giá trị bản ghi tương ứng ở bảng con là NULL khi dữ liệu trong bảng mẹ bị xóa. Ở ví dụ này, khi id_sanpham bị xóa khỏi bảng sanpham thì bản ghi tương ứng ở bảng hangtonkho dùng id_sanpham đó sẽ được đặt thành NULL.

Điều quan trọng cần lưu ý là vì cột id_sanpham trong bảng hangtonkho sẽ được đặt thành NULL nên cần đảm bảo cột này được phép nhận giá trị NULL. Nếu đặt là NOT NULL khi tạo bằng lệnh CREATEA thì sẽ bị lỗi dưới đây.

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.

Hãy chắc chắn rằng bạn định nghĩa cột id_sanpham trong bảng hangtonkho là được phép nhận giá trị NULL như dưới đây.

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 đặt giá trị NULL khi xóa bằng lệnh ALTER TABLE

Cú pháp

ALTER TABLE bang_con
ADD 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;

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

Chỉ định rằng dữ liệu con sẽ được đặt thành giá trị NULL khi dữ liệu trong bảng mẹ bị xóa. Dữ liệu ở bảng con sẽ không bị xóa.

Ví dụ

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

Trong ví dụ 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.

Chỉ định ON DELETE SET NULL cho SQL Server hiểu rằng khi dữ liệu id_sanpham trong bảng mẹ sanpham bị xóa thì bản ghi tương ứng ở bảng con hangtonkho sẽ được đặt thành giá trị NULL.

Bài trước: Khóa ngoại Foreign Key (Cascade Delete) trong SQL Server

Bài sau: Xóa khóa ngoại trong SQL Server