และหนึ่งใน Key ประกอบมีความเป็น Unique Subset เกิดขึ้น นั่นคือ EmpID เป็น Unique ทำให้ไม่เป็น Candidate Key
Primary Key
หมายถึง Key ที่ถูกเลือกมาจาก Candidate Key โดยมีหลักเกณฑ์ดังนี้
- ต้องไม่มีค่าซ้ำกันในแต่ละแถว
- ต้องไม่มีค่าเป็น NULL (ค่าว่าง)
- Primary Key จะถูกเปลี่ยนแปลงค่าไม่ได้
พิจารณา Candidate Key ที่เรามีอยู่
ฉะนั้นจึงเลือก EmpID เป็น Primary Key
Alternate Key
หมายถึง Key ที่ไม่ได้ถูกเลือกให้เป็น Primary Key นั่นก็คือ FirstName + LastName + DepID + Position
Foreign Key
หมายถึง Key ที่อ้างไปยัง Primary Key ของ Table หนึ่ง เช่น ใน Table Employees DepID ถือเป็น Foreign Key เพราะ DepID ได้อ้างไปยัง Primary Key ของอีก Table หนึ่ง
หมายเหตุ
เป็นบทความที่ถูกย้ายมาจาก //na5cent.blogspot.com/2011/12/keys-database.html ซึ่งผู้เขียน เขียนไว้เมื่อ วันศุกร์ที่ 16 ธันวาคม พ.ศ. 2554
สวีดัสสส .. สวัสดีครับ วันนี้ตั้งใจจะมาแชร์ Tip & Trick เรื่อง disable FK ทุก table Database มันทำยังไง เหตุผลที่ต้องมาเขียน Blog นี้เพราะว่า ผมเป็นคนขี้ลืม จริงๆเคยเขียนลบ.. เขียนลบ มาหลายรอบแล้ว พอสุดท้ายมีพี่ๆน้องๆเพื่อนร่วมงานถามมา ก็ลืมอีกตามเคย .. เลยขอแยกมาเขียนเก็บไว้เลยดีกว่า ก็ไม่มีอะไรมากครับ ถ้าใครรู้แล้วผ่านได้เลย ถ้าใครยังไม่รู้มาดูกัน..
ก่อนอื่นขอผมอธิบายและจำกัดความของ Foreign Key Constraint หรือเรียกสั้นๆว่า FK ว่ามันคืออะไร ก่อนนะครับ
โดยปกติแล้วการ Design DB ที่เป็นพวก RDBMS จะมีการใช้งานบางฟิลด์ข้อมูล เชื่อมโยงความสัมพันธ์ระหว่าง table ทำให้ต้องมีการคำนึงถึงเวลา เพิ่ม แก้ไข หรือ ลบฟิลด์ที่ใช้เชื่อมนั้น ไม่เช่นนั้นอาจจะทำให้ข้อมูลใน Database ไม่ Consistency และ ไม่สามารถในข้อมูลไปประมวลผลจนได้ค่าที่ถูกต้องได้ ซึ่งการนำ FK เข้ามาใช้งานจะช่วยให้แน่ใจว่าข้อมูลที่ใส่เข้าไป หรือ มีการแก้ไข มีความสมบูรณ์ถูกต้องของความสัมพันธ์ระหว่าง 2 ตาราง อย่างแน่นอน
ซึ่ง MSSQL ก็เป็น RDBMS ตัวนึงที่ support Constraint FK (ไม่ support ก็ไม่ใช่ RDBMS ละ -*-)
ทีนี้.. ปัญหาที่คนใช้งาน MSSQL เจอกันส่วนใหญ่ คือเวลาที่เราอยากจะ Load Data หรือนำเอา ETL tool ซักตัวมาใช้งานกับ Existing Database ก็มักจะติด FK เสมอ และ ไม่รูจะ Disable ยังไง เพราะไม่มี UI tool ให้เลือก Disable ผมก็จะมาแชร์ script สั้นๆที่ใช้งานอยู่ปัจจุบันนี้ไว้นะครับ
Example SQL Script --Disabling Constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"; --Enabling Constraints EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all";
เด๋วผมจะลองทดสอบให้ดู Query เช็ค FK ว่า Enable/Disable ง่ายๆจาก table sys.foreign_keys ครับ
อันนี้คือ Output ที่ผม Query ได้ก่อนรัน script disable นะครับ สังเกต Column is_disabled จะมี value = 0 นั่นคือ FK ยังเป็น Enable อยู่
Before
พอหลังจากรัน script disable ไปแล้ว is_disabled จะมี value = 1 นั่นนคือ FK ถูก Disable ไปแล้ว
After
ง่ายๆๆใช่ไหมครับ script ผมใช้ sp_msforeachtable เข้ามาช่วยซึ่งเป็น system stored procedure ที่จะช่วย fetch list table ใน database ขึ้นมา และ binding table name ด้วย ? เท่านี้ก็สามารถ disable all constraint ใน database ได้ละครับ
Notice:- อันนี้เสริมนิดนึง Option Check,NOCHECK อันนี้เป็นคนละอันกับ WITH CHECK , NOCHECK ที่เราใช้ตอน Create FK นะครับ
Option Check/NOCHECK ในที่นี้คือ Enable-Disable แต่ WITH CHECK/NOCHECK คือการเช็ค existing data ใน table ซึ่งมันแยกกันเน้อ เผื่อใครงง
Foreign Key (FK) คือ คอลัมน์หรือฟีลด์ในตารางที่ใช้สร้างความสัมพันธ์ระหว่างข้อมูลทั้งสองตารางเข้าด้วยกัน (หรือ Join กันนั่นเอง)โดย FK (ทำหน้าที่เป็นตารางลูกหรือ Child Table) จะอ้างอิงไปยัง Primary Key (PK) ที่จะทำหน้าที่เสมือนตารางแม่ (Parent Table) โดยปกติแล้วจะเป็นการเชื่อมความสัมพันธ์แบบ 1-to-M (One-to-Many หรือ Many-to-One)
ตัวอย่างการใช้งาน FK ผมจะขอยกตัวอย่างฐานข้อมูลบางส่วนของเว็บ E-commerce โดยจะมีการสร้างตาราง Buyer, Order และ Order Item ซึ่งเป็นตารางของเว็บขายสินค้าโดยทั่วไปเลยครับ จะเห็นว่า 1 buyer สามารถมีได้หลาย orders และ 1 order ก็ยังมีได้หลาย items เช่นกัน (ก็คือ 1-to-M) จากภาพด้านล่างแสดงผลไดอะแกรม (ใช้ dbdiagram.io)
ตาราง Buyer
buyer_id (PK) buyer_name 1 Som Jomson 2 Salid Narak 3 Korat Sudloh
ตาราง Order
order_id (PK) order_date total_price buyer_id (FK) 1 2023-10-28 2 1 2 2023-10-282 2 3 2023-10-285 3
ตาราง Order Items
order_item_id (PK) order_id (FK) item_name quantity price 1 1 อาหารเม็ด 2 300.00 2 1 อาหารเปียก 3 150.75 3 2 อาหารเปียก 5 251.25 4 3 ขนมแมวเลีย 9 180.00 5 3 ขนมแมวเลีย 8 160.00
จะเห็นว่าเราจะ join ตารางด้วยคอลัมน์ FK ไปที่คอลัมน์ PK หรือ Primary Key (คีย์หลัก) และแน่นอนว่า PK นั้นต้องเป็น ID ที่ unique คือต้องมีความเฉพาะเจาะจง ห้ามซ้ำกันเด็ดขาด (โดยส่วนใหญ่แล้วจะใช้ชนิดข้อมูลแบบ Int และเงื่อนไข Auto-increment)
เมื่อนำไปเขียนเป็นโค้ด SQL -- สร้างตาราง "Buyer" CREATE TABLE Buyer ( buyer_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, buyer_name VARCHAR(255) NOT NULL ); -- สร้างตาราง "Order" CREATE TABLE Order ( order_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, order_date DATE NOT NULL, total_price DECIMAL(10, 2) NOT NULL, buyer_id INT NOT NULL, FOREIGN KEY (buyer_id) REFERENCES Buyer(buyer_id) ); -- สร้างตาราง "Order Items" CREATE TABLE Order_Items ( order_item_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, order_id INT NOT NULL, item_name VARCHAR(255) NOT NULL, quantity INT NOT NULL, price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES Order(order_id) );
บทความแนะนำอ่านเพิ่มเติม: ภาษา SQL คืออะไร รวมคำสั่งใช้งาน SQL สำหรับผู้เริ่มต้น
เงื่อนไข ON DELETE แล้วถ้าต้องมีการลบแถวหรือ Record ภายในตาราง Primary ล่ะ อะไรจะเกิดขึ้น ?
ตามปกติแล้วเมื่อกำหนด FOREIGN KEY จะไม่สามารถลบข้อมูลใน Primary Key ได้ ดังนั้นต้องมีการกำหนด Constraint (เงื่อนไข) ก่อนว่า เมื่อกดลบแล้วจะมีอะไรเกิดขึ้นต่อไป เวลานำไปใช้งานก็ลองเลือกเงื่อนไขที่เข้ากับบริบทของ use case จริงกันด้วยนะครับ ซึ่งคำสั่ง ON DELETE ก็มีอยู่ทั้งหมด 5 ตัว ดังนี้
- CASCADE
- RESTRICT
- ACTION
- SET NULL
- SET DEFAULT
ON DELETE CASCADE
คำสั่ง ON DELETE CASCADE เป็นคำสั่งที่เมื่อเราลบ ID ตัวใดตัวหนึ่งของ PRIMARY KEY ออกแล้ว ข้อมูลในทุก ๆ Row ของ ID นั้น ๆ จะถูกลบตามไปด้วยเช่นกัน
ถ้าใครที่ใช้ Back-end เฟรมเวิร์ค ตัวอย่างเช่นในภาพด้านล่างคือ Django framework จะเห็นว่ามีการกำหนดเงื่อนไข on_delete ใน FK ตามแนวคิดที่ได้อธิบายไปข้างต้นเลยครับ (แต่ที่เห็นเขียนแปลกจาก SQL ไปบ้างเพราะว่าเป็นการเขียนในรูปแบบ ORM: Object Relational Mapping)
ปล.ใน คอร์สสอนทำเว็บไซต์ด้วย Django Python แบบตัวต่อตัว เราก็มีสอน FK ด้วยครับ
ON DELETE RESTRICT
คำสั่ง ON DELETE RESTRICT เป็นคำสั่งที่ไม่อนุญาตให้มีการลบแถว (Record)ใด ๆ ก็ตามในตารางที่เป็น Primary Table
ON DELETE NO ACTION
คำสั่ง ON DELETE NO ACTION เป็นคำสั่งที่ป้องกันการลบแถวใน Primary Table คล้ายกับ RESTRICT มาก ๆ เลยครับ เพื่อน ๆ อาจจะลองเข้าไปอ่านเพิ่มในใน Doc ของ PostgreSQL ดูเพิ่มเติมได้
ON DELETE SET NULL
คำสั่ง ON DELETE SET NULL เป็นคำสั่งที่ชัดเจนตามชื่อเลยครับ เมื่อมีการลบแถวใน Primary Table ค่าจะเปลี่ยนเป็น Null ทันที
ON DELETE SET DEFAULT
คำสั่ง ON DELETE SET DEFAUTL เป็นคำสั่งที่ค่อนข้างคล้ายกับ Null คือเมื่อมีการลบแถวของ Primary Table ค่าจะถูกคืนกลับไปเป็นค่าเริ่มต้น (Default) ซึ่งถ้าหากจะใช้งานคำสั่งนี้ เราจะต้องกำหนดค่า Default ไว้ก่อนครับ
ประโยชน์ของ Foreign Key ?
- ช่วยในการรักษาความสมบูรณ์และความถูกต้องของข้อมูล (Data Integrity & Accuracy) จากการเชื่อมความสัมพันธ์ระหว่างตาราง ข้อมูลสอดคล้องตรงกัน สมมาตรกันทุกจุด เพราะมีจุดอ้างอิงเดียวกัน แม้การแก้ไขข้อมูลเพียงจุดเดียว ก็ไม่ต้องตามไปแก้ในทุก ๆ ฟีลด์ของ record เพื่อให้ข้อมูลกลับมาตรงกัน (ซึ่งทำให้เกิดโอกาสผิดพลาดได้สูงมากครับ)
- ลดปัญหาความซ้ำซ้อนของข้อมูล
- ทำให้การ query ที่ซับซ้อน หรือไม่ว่าจะเป็นการ sort, search ข้อมูล ได้รวดเร็ว (แต่จะมีข้อเสียตรงที่การ INSERT, UPDATE, DELETE จะช้า)
ก็จบไปแล้วครับสำหรับแนวคิดของ Foreign Key และตัวอย่างการใช้งานจริง หวังว่าจะทำให้เพื่อน ๆ ที่อ่าน มองภาพรวมและสามารถนำแนวคิดและการออกแบบในบทความนี้ไปประยุกต์ใช้งานจริงได้ครับ