CONSTRAINT: tính toàn vẹn của cơ sở dữ liệu

Kiểm soát toàn vẹn cơ sở dữ liệu

Một điều quan trọng khác cần biết về cơ sở dữ liệu là CONSTRAINS. Với sự trợ giúp của các ràng buộc, bạn có thể kiểm soát các thay đổi dữ liệu trong bảng của mình và duy trì tính toàn vẹn và nhất quán của chúng.

Tính nhất quán của dữ liệu khi chúng ta nói về cơ sở dữ liệu là gì ?

Hãy tạo cửa hàng trực tuyến của chúng ta với các bảng nhân viên, sản phẩm và nhiệm vụ . Chúng ta đã biết rằng có thể có các nhiệm vụ trong bảng nhiệm vụ không được giao cho bất kỳ ai: employee_id của các hàng đó là NULL.

Nhưng điều gì sẽ xảy ra nếu có một mục trong bảng nhiệm vụ có employee_id bằng, chẳng hạn như 115? Rốt cuộc, chúng tôi không có một nhân viên như vậy. Chúng tôi không có nhân viên với id = 115 trong bảng nhân viên. Đồng thời, một liên kết đến một nhân viên có id này nằm trong bảng nhiệm vụ. Đây là một ví dụ về dữ liệu không nhất quán .

Vì vậy, làm thế nào để chúng tôi đối chiếu những dữ liệu này? Lý tưởng nhất là máy chủ SQL, với bất kỳ thay đổi dữ liệu nào, sẽ kiểm soát tất cả các sắc thái này. Và có một cơ hội như vậy, nó được gọi là FOREIGN_KEY.

Nếu một số cột trong bảng của bạn không chỉ chứa các số mà còn chứa các hàng id từ một bảng khác, thì cột này có thể được chỉ định rõ ràng.

Thêm một NGOẠI KHÓA

Một khóa như vậy có thể được thêm vào bảng cả ở giai đoạn tạo và sau đó, sử dụng ALTER TABLE. Định dạng không khác nhau về cơ bản. Chúng tôi sẽ trình bày cả hai lựa chọn.

Hình thức chung của một khóa/quy tắc như vậy là:

FOREIGN KEY (column)
  	REFERENCES table(column)

Hãy thêm khóa/quy tắc này vào bảng tác vụ để đảm bảo rằng tất cả các employee_id từ bảng tham chiếu đến một mục nhập hiện có trong bảng nhân viên. Kịch bản này sẽ trông như thế này:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Và nếu chúng tôi quyết định thêm quy tắc này vào thời điểm tạo bảng tác vụ, thì mã sẽ trông như thế này:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Nhân tiện, có những trường hợp khi chuỗi mà chúng tôi đề cập đến có một khóa tổng hợp duy nhất: ví dụ: “Tên và năm sinh” hoặc “productCatogoryId và productId”. Sau đó, FOREIGN KEY có thể được viết như sau:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

FOREIGN KEY và thay đổi dữ liệu

Bây giờ hãy tưởng tượng một tình huống mà chúng tôi quyết định cập nhật một số dữ liệu trong bảng nhân viên và id nhân viên của chúng tôi đã thay đổi. Điều gì sẽ xảy ra với dữ liệu trong bảng nhiệm vụ? Đúng vậy, chúng sẽ trở nên không liên quan và tính toàn vẹn của cơ sở dữ liệu của chúng tôi sẽ bị vi phạm.

Để ngăn điều này xảy ra, bạn có thể yêu cầu SQL Server thay đổi employee_id của tất cả các hàng trong tất cả các bảng tham chiếu đến id đã thay đổi cụ thể này khi id trong bảng nhân viên thay đổi.

Các tập lệnh như vậy được gọi là OnUpdate và OnDelete . Phải làm gì nếu id bản ghi thay đổi và phải làm gì nếu bản ghi bị xóa?

Với việc loại bỏ, không phải mọi thứ đều đơn giản như vậy. Nếu bạn có các đối tượng phụ thuộc được đại diện bởi các chuỗi trong cơ sở dữ liệu tham chiếu đến nhau, thì có thể có nhiều tình huống hành vi khác nhau khi xóa một đối tượng.

Giả sử chúng ta xóa một người dùng trang web, nghĩa là chúng ta phải xóa tất cả thư từ cá nhân của anh ta. Nhưng không chắc là chúng ta nên xóa tất cả các bình luận công khai của anh ấy.

Hoặc một nhân viên nghỉ việc. Sẽ thật kỳ lạ nếu anh ta nghỉ việc và đồng thời tất cả các nhiệm vụ được giao cho anh ta biến mất khỏi cơ sở dữ liệu. Nhưng nếu họ vẫn không được bổ nhiệm bởi anh ta, thì mọi chuyện cũng sẽ trở nên tồi tệ. Sẽ đúng hơn nếu làm cho nhân viên có thể nghỉ việc sau khi giao lại tất cả các nhiệm vụ của mình cho người khác.

Đây là cách chúng ta có thể mô tả các tình huống này bằng FOREIGN KEY. Hình thức chung của một khóa/quy tắc như vậy là:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Cần làm gì trong trường hợp xóa (ON DELETE) hoặc thay đổi (ON UPDATE) bản ghi? Tổng cộng, có thể có 5 tùy chọn để máy chủ SQL hành động trong từng tình huống sau:

Đây là cách chúng tôi có thể sửa đổi bảng nhiệm vụ của mình:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Những gì được viết ở đây:

TRÊN CẬP NHẬT CASCADE : Nếu khóa id trong bảng nhân viên thay đổi, thì cũng thay đổi employee_id trong bảng nhiệm vụ tham chiếu đến nó.

BẬT GIỚI HẠN XÓA : Nếu một hàng đang bị xóa khỏi bảng nhân viên và được tham chiếu từ bảng nhiệm vụ, thì hãy ngăn không cho hàng đó bị xóa khỏi bảng nhân viên.