ข้อจำกัด: ความสมบูรณ์ของฐานข้อมูล

การควบคุมความสมบูรณ์ของฐานข้อมูล

สิ่งสำคัญอีกอย่างที่ต้องรู้เกี่ยวกับฐานข้อมูลคือ CONSTRAINS ด้วยความช่วยเหลือของข้อจำกัด คุณสามารถควบคุมการเปลี่ยนแปลงข้อมูลในตารางของคุณ และรักษาความสมบูรณ์และความสอดคล้องได้

ความสอดคล้องของข้อมูลเมื่อเราพูดถึงฐานข้อมูลคืออะไร ?

ลองนำร้านค้าออนไลน์ของเราที่มีตารางพนักงาน ผลิตภัณฑ์ และงาน เรารู้อยู่แล้วว่าอาจมีงานในตารางงานที่ไม่ได้มอบหมายให้ใครเลย: รหัสพนักงานของแถวดังกล่าวคือ NULL

แต่จะเกิดอะไรขึ้นถ้ามีรายการในตารางงานที่มี Employee_id เท่ากับ 115 ท้ายที่สุดเราไม่มีพนักงานดังกล่าว เราไม่มีพนักงานที่มี id = 115 ในตารางพนักงาน ในขณะเดียวกัน ลิงก์ไปยังพนักงานที่มีรหัสนี้จะอยู่ในตารางงาน นี่คือตัวอย่างของ ความไม่สอดคล้อง กันของข้อมูล

แล้วเราจะกระทบยอดข้อมูลเหล่านี้ได้อย่างไร? ตามหลักการแล้ว เซิร์ฟเวอร์ SQL จะควบคุมความแตกต่างเหล่านี้เมื่อมีการเปลี่ยนแปลงข้อมูลใดๆ และมีโอกาสเช่นนี้ เรียกว่า FOREIGN_KEY

หากบางคอลัมน์ในตารางของคุณไม่ได้มีแค่ตัวเลข แต่ยังมีแถวรหัสจากตารางอื่นด้วย คุณสามารถระบุสิ่งนี้ได้อย่างชัดเจน

การเพิ่มคีย์ต่างประเทศ

คีย์ดังกล่าวสามารถเพิ่มลงในตารางได้ทั้งในขั้นตอนของการสร้างและหลังจากนั้นโดยใช้ ALTER TABLE รูปแบบไม่แตกต่างกันโดยพื้นฐาน เราจะนำเสนอทั้งสองตัวเลือก

รูปแบบทั่วไปของคีย์/กฎดังกล่าวคือ:

FOREIGN KEY (column)
  	REFERENCES table(column)

เรามาเพิ่มคีย์/กฎนี้ใน ตาราง งานเพื่อให้แน่ใจว่าEmployee_ids ทั้งหมด จากตารางอ้างอิงถึงรายการที่มีอยู่ในตารางพนักงาน สคริปต์นี้จะมีลักษณะดังนี้:

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

และถ้าเราตัดสินใจเพิ่มกฎนี้ในขณะที่สร้างตารางงาน โค้ดจะมีลักษณะดังนี้:

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

อย่างไรก็ตาม มีบางสถานการณ์ที่สตริงที่เราอ้างถึงมีคีย์ผสมที่ไม่ซ้ำกัน ตัวอย่างเช่น “ชื่อและปีเกิด” หรือ “productCatogoryId และ productId” จากนั้น FOREIGN KEY สามารถเขียนได้ดังนี้:

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

FOREIGN KEY และการเปลี่ยนแปลงข้อมูล

ลองนึกภาพสถานการณ์ที่เราตัดสินใจอัปเดตข้อมูลบางอย่างในตารางพนักงานและรหัสพนักงานของเราเปลี่ยนไป จะเกิดอะไรขึ้นกับข้อมูลในตารางงาน? ถูกต้อง พวกเขาจะไม่เกี่ยวข้องและความสมบูรณ์ของฐานข้อมูลของเราจะถูกละเมิด

เพื่อป้องกันไม่ให้สิ่งนี้เกิดขึ้น คุณสามารถบอก SQL Server ให้เปลี่ยน Employee_id ของแถวทั้งหมดในตารางทั้งหมดที่อ้างถึง id ที่เปลี่ยนแปลงโดยเฉพาะนี้ เมื่อ id ในตารางพนักงานเปลี่ยนไป

สคริปต์ดังกล่าวเรียกว่าOnUpdateและOnDelete จะทำอย่างไรถ้า ID ของเรกคอร์ดเปลี่ยนไป และจะทำอย่างไรถ้าเรกคอร์ดถูกลบ

ด้วยการลบไม่ใช่ทุกอย่างจะง่ายนัก หากคุณมีวัตถุที่อ้างอิงซึ่งแสดงด้วยสตริงในฐานข้อมูลที่อ้างอิงถึงกันและกัน สถานการณ์จำลองลักษณะการทำงานที่หลากหลายจะเป็นไปได้เมื่อลบวัตถุหนึ่งชิ้น

สมมติว่าเราลบผู้ใช้ไซต์ ซึ่งหมายความว่าเราต้องลบการติดต่อส่วนบุคคลทั้งหมดของเขา แต่ไม่น่าเป็นไปได้ที่เราจะลบความคิดเห็นสาธารณะทั้งหมดของเขา

หรือลูกจ้างลาออก. คงจะแปลกถ้าเขาลาออกและในขณะเดียวกันงานทั้งหมดที่มอบหมายให้เขาก็หายไปจากฐานข้อมูล แต่ถ้าพวกเขาไม่ได้รับการแต่งตั้งจากเขา มันก็จะกลับกลายเป็นเรื่องเลวร้ายเช่นกัน ถูกต้องกว่าที่จะทำให้พนักงานสามารถลาออกได้หลังจากมอบหมายงานทั้งหมดให้กับคนอื่น

นี่คือวิธีที่เราสามารถอธิบายสถานการณ์เหล่านี้โดยใช้ FOREIGN KEY รูปแบบทั่วไปของคีย์/กฎดังกล่าวคือ:

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

จะทำอย่างไรในกรณีที่มีการลบ (ON DELETE) หรือเปลี่ยนแปลง (ON UPDATE) บันทึก? โดยรวมแล้ว อาจมี 5 ตัวเลือกสำหรับเซิร์ฟเวอร์ SQL เพื่อดำเนินการในแต่ละสถานการณ์เหล่านี้:

นี่คือวิธีที่เราสามารถแก้ไขตารางงานของเรา:

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

สิ่งที่เขียนที่นี่:

ON UPDATE CASCADE : ถ้าคีย์ id ในตารางพนักงานเปลี่ยนไป ให้เปลี่ยน Employee_id ในตารางงานที่อ้างอิงถึงมันด้วย

ON DELETE RESTRICT : หากแถวถูกลบออกจากตารางพนักงานและอ้างอิงจากตารางงาน ให้ป้องกันไม่ให้แถวนั้นถูกลบออกจากตารางพนักงาน