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
CREATETABLE 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
);
Comma
nd(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.
16 Th12 2020
31 Th12 2020
8 Th1 2021
6 Th1 2021
25 Th12 2020
16 Th12 2020