เขียนเว็บ เดอะ ซีรีส์ ตอนที่ 34: การกำหนดเงื่อนไข (Constraint) เพื่อเชื่อมโยงข้อมูลระหว่างตาราง

ในตอนที่ 32 และ 33 เราได้มีการอธิบายเกี่ยวกับการกำหนดคีย์หลักของตาราง (Primary Key) และการกำหนดคีย์นอกของตาราง (Foreign Key) ไปแล้ว แต่ยังไม่มีการเชื่อมโยงข้อมูลระหว่างตารางซึ่งจะมีผลต่อการเพิ่ม ลบ หรือแก้ไขข้อมูลในตาราง การจะเชื่อมโยงข้อมูลระหว่างตารางจะต้องกำหนดเงื่อนไข หรือ Constraint ให้กับคอลัมน์ในแต่ละตารางก่อน

สำหรับตอนที่ 34 นี้ เราจะกำหนด Constriant ให้กับตาราง airport_list, province, responsible_agency ของฐานข้อมูล airport_thai_db จากตอนที่ 33

การเชื่อมโยงข้อมูลระหว่างตาราง

จากตอนที่ 33 จะพบว่าตาราง airport_list มีอยู่ 2 คอลัมน์ ที่มีการเก็บข้อมูลชุดเดียวกันกับตารางอื่น ๆ ได้แก่

1. คอลัมน์ province เก็บข้อมูลรหัสจังหวัดเช่นเดียวกันกับคอลัมน์ province_code ในตาราง province
2. คอลัมน์ agency เก็บข้อมูลรหัสหน่วยงานที่รับผิดชอบเช่นเดียวกันกับคอลัมน์ agency_code ในตาราง responsible_agency

ทั้ง 2 คอลัมน์นี้ถือว่าเป็น Foreign Key ของตาราง airport_list และสามารถเชื่อมโยงข้อมูลกับตาราง province และตาราง responsible_agency ได้ โดยเราได้มีการกำหนด Foreign Key ให้กับตาราง airport_list เรียบร้อยแล้วในตอนที่ 33

ตาราง airport_list ที่มี Foreign Key ในคอลัมน์ province และ agency (มีรูปกุญแจสีเทาข้าง ๆ ชื่อคอลัมน์)

การเชื่อมโยงข้อมูลระหว่างตาราง

ก่อนที่จะทำการเชื่อมโยงข้อมูลระหว่างตาราง เราต้องเลือกตาราง 2 ตาราง (หรือมากกว่า) ที่มีคอลัมน์หนึ่งที่มีข้อมูลตรงกัน โดยคอลัมน์หนึ่งเป็นคอลัมน์ที่มีข้อมูลซ้ำกัน (คอลัมน์ที่เป็น Foreign Key) และอีกคอลัมน์หนึ่งเป็นคอลัมน์ที่มีข้อมูลไม่ซ้ำกัน (มักเป็นคอลัมน์ที่เป็น Primary Key)

จากฐานข้อมูล airport_thai_db จะพบว่าในตาราง airport_list มีคอลัมน์ที่มีข้อมูลซ้ำกันกับตารางอื่น ๆ ดังนี้

1. คอลัมน์ province มีข้อมูลซ้ำกับคอลัมน์ province_code (เป็น Primary Key) ในตาราง province
2. คอลัมน์ agency มีข้อมูลซ้ำกับคอลัมน์ agency_code (เป็น Primary Key) ในตาราง responsible_agency

คอลัมน์ province และ agency จึงถูกกำหนดเป็น Foreign Key ให้กับตาราง airport_list เพื่อเชื่อมโยงไปยังคอลัมน์ (ที่เป็น Primary Key) ของตารางที่เกี่ยวข้อง

วิธีการเชื่อมโยงข้อมูลระหว่างตารางต่าง ๆ เริ่มต้นให้มาที่หน้า Structure ของตาราง airport_list แล้วเลือก Relation View (กรอบสีแดงตามภาพ)

เลือก Relation View เพื่อเริ่มเชื่อมโยงข้อมูลระหว่างตารางต่าง ๆ

เมื่อเลือก Relation View จะพบกับหน้า Foreign key constraints โดยจะมีช่องต่าง ๆ ให้กรอกเพื่อสร้างการเชื่อมโยงข้อมูลระหว่างตาราง

หน้า Foreign key constraints สำหรับการสร้างการเชื่อมโยงข้อมูลระหว่างตาราง

ช่อง Constraint name สำหรับการตั้งชื่อ constraint ซึ่งถ้าเราไม่ตั้งชื่อระบบจะตั้งชื่อให้เองโดยอัตโนมัติ

ช่อง Constraint name สำหรับการตั้งชื่อ constraint

ส่วนของ ON DELETE ให้เลือกเป็น CASCADE ในกรณีที่มีการลบข้อมูลในตาราง province หรือ responsible_agency จะทำให้ข้อมูลในตาราง airport_list ถูกลบไปด้วย

ON DELETE ให้เลือกเป็น CASCADE

ส่วนของ ON UPDATE ให้เลือกเป็น CASCADE ในกรณีที่มีการแก้ไขข้อมูลในตาราง province หรือ responsible_agency จะทำให้ข้อมูลในตาราง airport_list ถูกแก้ไขให้เหมือนกัน

ON UPDATE ให้เลือกเป็น CASCADE

ข้อมูลเพิ่มเติม:

• ON DELETE และ ON UPDATE จะมีอยู่ 4 ฟังก์ชันให้เลือก คือ

1. CASCADE: ถ้ามีการลบหรือแก้ไขข้อมูลในตารางที่มี Primary Key จะทำให้ข้อมูลในตารางที่มี Foreign Key ถูกลบหรือแก้ไขทั้งหมด
2. SET NULL: ถ้ามีการลบหรือแก้ไขข้อมูลในตารางที่มี Primary Key จะทำให้ข้อมูลในตารางที่มี Foreign Key ถูกเปลี่ยนเป็น NULL ทั้งหมด โดยคอลัมน์ที่เป็น Foreign Key จะต้องเก็บค่า NULL ในตารางได้
3. NO ACTION: ไม่ว่าข้อมูลในตารางที่มี Primary Key จะถูกลบหรือแก้ไขหรือไม่ ข้อมูลในตารางที่มี Foreign Key จะไม่มีการเปลี่ยนแปลง
4. RESTRICT: ถ้าตารางที่มี Foreign Key ยังมีข้อมูลอยู่จะไม่สามารถลบหรือแก้ไขข้อมูลในตารางที่มี Primary Key ได้

หัวข้อ Column ให้เลือกคอลัมน์ที่เป็น Foreign Key โดยรอบนี้จะเลือกคอลัมน์ province

หัวข้อ Column ให้เลือกคอลัมน์ province ที่เป็น Foreign Key

ข้อมูลเพิ่มเติม:

• การสร้าง Constraint เพื่อเชื่อมโยงระหว่างตารางควรเลือกคอลัมน์เพียง 1 คอลัมน์ต่อ 1 Constraint

หัวข้อ Database ให้เลือกฐานข้อมูล airport_thai_db

หัวข้อ Database ให้เลือกฐานข้อมูล airport_thai_db

หัวข้อ Table ให้เลือกตาราง province เพราะมีคอลัมน์ province_no ที่มีข้อมูลรหัสจังหวัดเช่นเดียวกันกับคอลัมน์ province ของตาราง airport_list

หัวข้อ Table ให้เลือกตาราง province

เมื่อเลือกตาราง province แล้ว ระบบจะเลือกให้เชื่อมกับคอลัมน์ province_code ที่เป็น Primary Key โดยอัตโนมัติ เนื่องจากตาราง province มีคอลัมน์เดียวที่มีคีย์

ตาราง province มีคอลัมน์ province_code คอลัมน์เดียวที่มีคีย์ ระบบจึงเลือกคอลัมน์ province_code มาให้โดยอัตโนมัติ

ข้อมูลเพิ่มเติม:

• ถ้าตารางที่ต้องการเชื่อมโยงข้อมูลมีหลายคอลัมน์ที่มีคีย์ ให้เลือกคอลัมน์เดียวที่ต้องการเชื่อมโยงมีคีย์เพียงคีย์เดียวระบบจะเชื่อมต่อกับคอลัมน์ที่มีคีย์นั้นโดยอัตโนมัติ แต่ถ้าตารางมีหลายคีย์ควรเลือกคอลัมน์ที่เป็น Primary Key

ต่อไปเป็นการสร้าง Constraint ตัวที่ 2 เพื่อเชื่อมกับตาราง responsible_agency ให้คลิก Add constraint เพื่อสร้าง Constraint เพิ่ม (ถ้าไม่มีการสร้าง Constraint เพิ่มให้คลิก Save ได้เลย)

คลิก Add constraint เพื่อสร้าง Constraint เพิ่ม

จากนั้นให้ทำตามวิธีเดียวกันกับตอนที่สร้าง Constraint ก่อนหน้านี้ โดยตั้งค่าให้ ON DELETE และ ON UPDATE เป็น CASCADE ทั้งคู่ และเลือกคอลัมน์ agency ให้เชื่อมโยงกับคอลัมน์ agency_code ของตาราง responsible_agency (กรอบสีแดงตามภาพ)

สร้าง Constraint สำหรับการเชื่อมโยงข้อมูลระหว่างตาราง airport_list กับตาราง responsible_agency

เมื่อกำหนด Constraint ครบทั้งหมดแล้วให้คลิก Save โดยระบบจะแจ้งว่าได้สร้างการเชื่อมโยงระหว่างตารางเรียบร้อยแล้ว

เมื่อคลิก Save ระบบจะแจ้งว่าได้สร้างการเชื่อมโยงระหว่างตารางเรียบร้อยแล้ว

ถ้าต้องการลบ Constraint ให้คลิก Drop ตรง Constraint ที่ต้องการลบ (กรอบสีแดงตามภาพ)

ถ้าต้องการลบ Constraint ให้คลิก Drop ตรง Constraint ที่ต้องการลบ

ระบบจะถามว่าต้องการลบ Constraint นี้หรือไม่ ให้คลิก OK เพื่อลบ Constraint

ระบบจะถามว่าต้องการลบ Constraint นี้หรือไม่ ให้คลิก OK เพื่อลบ Constraint

ข้อสำคัญในการสร้างตาราง

ข้อสำคัญในการสร้างตาราง คือ ควรจะกำหนด Primary Key, Foreign Key พร้อมทั้งสร้าง Constraint เพื่อเชื่อมโยงข้อมูลระหว่างตารางให้เสร็จเรียบร้อยก่อนที่จะทำการเพิ่ม ลบ หรือแก้ไขข้อมูลภายในตาราง

เพิ่มข้อมูลลงในแต่ละตาราง

เมื่อมีการเชื่อมโยงข้อมูลระหว่างตาราง airport_list กับตาราง province และตาราง responsible_agency แล้ว ให้เพิ่มข้อมูลลงในตาราง province และตาราง responsible_agency ก่อน เพราะเป็นตารางที่มี Primary Key (ดูวิธีการเพิ่มข้อมูลในตอนที่ 30: SELECT, INSERT, UPDATE, DELETE)

ตัวอย่างข้อมูลในตาราง province

ตัวอย่างข้อมูลในตาราง responsible_agency

หลังจากนั้นให้ทำการเพิ่มข้อมูลลงในตาราง airport_list โดยการพิมพ์คำสั่ง SQL ดังนี้

INSERT INTO airport_list (airport_iata, airport_icao, airport_name_th, airport_name_en, province, agency) VALUES ('BKK', 'VTBS', 'ท่าอากาศยานสุวรรณภูมิ', 'Suvarnabhumi Airport', 11, 1),
('DMK', 'VTBD', 'ท่าอากาศยานดอนเมือง', 'Don Mueang International Airport', 10, 1),
('CNX', 'VTCC', 'ท่าอากาศยานเชียงใหม่', 'Chiang Mai International Airport', 50, 1),
('CEI', 'VTCT', 'ท่าอากาศยานแม่ฟ้าหลวง เชียงราย', 'Mae Fah Luang - Chiang Rai International Airport', 57, 1),
('UTH', 'VTUD', 'ท่าอากาศยานนานาชาติอุดรธานี', 'Udon Thani International Airport', 41, 2),
('UBP', 'VTUU', 'ท่าอากาศยานนานาชาติอุบลราชธานี', 'Ubon Ratchathani International Airport', 34, 2),
('UTP', 'VTBU', 'ท่าอากาศยานนานาชาติอู่ตะเภา', 'U-Tapao International Airport', 41, 4),
('URT', 'VTSB', 'ท่าอากาศยานนานาชาติสุราษฎร์ธานี', 'Surat Thani International Airport', 84, 2),
('USM', 'VTSM', 'ท่าอากาศยานนานาชาติสมุย', 'Samui International Airport', 84, 3),
('HDY', 'VTSS', 'ท่าอากาศยานหาดใหญ่', 'Hat Yai International Airport', 90, 1);

เพิ่มข้อมูลลงในตาราง airport_list

เนื่องจากว่าเราไม่ได้เพิ่มข้อมูลลงในทุกคอลัมน์ เพราะคอลัมน์ airport_code เป็น Auto Increment เวลาที่มีการพิมพ์คำสั่ง INSERT INTO จะต้องระบุชื่อคอลัมน์ที่ต้องการเพิ่มข้อมูล และสำหรับคำสั่ง VALUES จะต้องเพิ่มข้อมูลให้ตรงตามจำนวนคอลัมน์ที่ระบุเอาไว้ด้วย จึงจะสามารถเพิ่มข้อมูลลงในตารางได้

เมื่อคลิก Go ให้คำสั่งทำงานแล้ว ตาราง airport_list จะมีข้อมูลตามภาพด้านล่าง

รายการข้อมูลในตาราง airport_list

ที่คอลัมน์ province และ agency จะพบว่าข้อมูลจะแสดงเป็น Hyperlink (ตัวอักษรสีน้ำเงินที่เราสามารถคลิกเพื่อไปยังหน้าต่าง ๆ ได้) ทั้งหมด (กรอบสีแดงตามภาพ)

เมื่อสร้าง constraint แล้ว ข้อมูลทั้งหมดในคอลัมน์ province และ agency จะแสดงเป็น Hyperlink

ถ้าอยากรู้ว่าข้อมูลนี้จะพาเราไปที่ตารางใดให้คลิกที่ข้อมูลสักข้อมูลหนึ่ง โดยตัวอย่างนี้เลือกข้อมูล “10” ของคอลัมน์ province (กรอบสีแดงตามภาพ)

คลิก “10” ในคอลัมน์ province เพื่อดูว่ามาจากอะไร

เมื่อคลิก “10” ระบบจะแสดงให้ทราบว่าค่า “10” คือ กรุงเทพมหานคร ในตาราง province

เมื่อคลิก “10” ระบบจะแสดงเป็น “กรุงเทพมหานคร” ในตาราง province

อีกตัวอย่างหนึ่งเป็นการเลือกข้อมูล “1” ของคอลัมน์ agency (กรอบสีแดงตามภาพ)

คลิก “1” ในคอลัมน์ agency เพื่อดูว่ามาจากอะไร

เมื่อคลิก “1” ระบบจะแสดงให้ทราบว่าค่า “1” คือ บริษัท ท่าอากาศยานไทย จำกัด (มหาชน) ในตาราง responsible_agency

เมื่อคลิก “1” ระบบจะแสดงเป็น “บริษัท ท่าอากาศยานไทย จำกัด (มหาชน)” ในตาราง responsible_agency

แก้ไขข้อมูลในตารางที่เป็น Primary Key

ตัวอย่างนี้เราต้องการแก้ไขข้อมูลจาก “จังหวัดสุราษฎร์ธานี” เป็น “จังหวัดนนทบุรี” โดย “จังหวัดสุราษฎร์ธานี” มีค่า province_code คือ “84” ซึ่งเท่ากับข้อมูล “84” ในคอลัมน์ province ของตาราง airport_list

ค่า “84” ในคอลัมน์ province ของตาราง airport_list มีทั้งหมด 2 รายการ

เมื่อคลิก “84” ในคอลัมน์ province ของตาราง airport_list จะแสดงเป็น “จังหวัดสุราษฎร์ธานี” ในตาราง province

ให้มาที่ตาราง province จากนั้นแก้ไขข้อมูลโดยเปลี่ยนจาก “จังหวัดสุราษฎร์ธานี” เป็น “จังหวัดนนทบุรี” ด้วยการพิมพ์คำสั่ง SQL ดังนี้

UPDATE province SET province_code = '12', province_name = 'นนทบุรี' WHERE province_code = '84';

แก้ไขข้อมูลจาก “จังหวัดสุราษฎร์ธานี” เป็น “จังหวัดนนทบุรี”

เมื่อคลิก Go ให้คำสั่งทำงานแล้ว “จังหวัดสุราษฎร์ธานี” จะถูกเปลี่ยนเป็น “จังหวัดนนทบุรี” (กรอบสีแดงตามภาพด้านล่าง)

ตาราง province แสดงให้เห็นว่าถูกแก้ไขข้อมูลเป็น “จังหวัดนนทบุรี” เรียบร้อยแล้ว

นอกจากนี้ที่ตาราง airport_list ข้อมูลในคอลัมน์ province ของ “ท่าอากาศนานาชาติสุราษฎร์ธานี” และ “ท่าอากาศยานนานาชาติสมุย” ถูกเปลี่ยนจาก “84” เป็น “12” (กรอบสีแดงตามภาพด้านล่าง)

ที่คอลัมน์ province ของตาราง airport_list ถูกแก้ไขข้อมูลจาก “84” เป็น “12”

การที่เรากำหนด Constraint ให้กับคอลัมน์ province ของตาราง airport_list ในส่วนของ ON UPDATE ให้เป็น ON CASCADE ทำให้เมื่อมีการแก้ไขข้อมูลในคอลัมน์ province_code ของตาราง province จะทำให้ข้อมูลในคอลัมน์ province ของตาราง airport_list ถูกเปลี่ยนแปลงไปด้วย

ลบข้อมูลในตารางที่เป็น Primary Key

ตัวอย่างนี้เราต้องการลบข้อมูล “กรมท่าอากาศยาน” โดยมีค่า agency_code คือ “2” ซึ่งเท่ากับข้อมูล “2” ในคอลัมน์ agency ของตาราง airport_list

ค่า “2” ในคอลัมน์ agency ของตาราง airport_list มีทั้งหมด 3 รายการ

เมื่อคลิก “2” ในคอลัมน์ agency ของตาราง airport_list จะแสดงเป็น “กรมท่าอากาศยาน” ในตาราง responsible_agency

ให้มาที่ตาราง responsible_agency จากนั้นลบข้อมูล “กรมท่าอากาศยาน” ด้วยการพิมพ์คำสั่ง SQL ดังนี้

DELETE FROM responsible_agency WHERE agency_code = '2';

ลบข้อมูล “กรมท่าอากาศยาน” ในตาราง responsible_agency

เมื่อคลิก Go ให้คำสั่งทำงานแล้วระบบจะทำการลบข้อมูล “กรมท่าอากาศยาน” ออกจากตาราง responsible_agency ตามภาพด้านล่าง

ตาราง responsible_agency แสดงให้เห็นว่า “กรมท่าอากาศยาน” ถูกลบเรียบร้อยแล้ว

นอกจากนี้รายการท่าอากาศยานทั้งหมดที่มีค่า agency เท่ากับ “2” ในตาราง airport_list จะถูกลบไปด้วยตามภาพด้านล่าง

รายการท่าอากาศยานทั้งหมดที่มีค่า agency เท่ากับ “2” ในตาราง airport_list จะถูกลบไปด้วย

การที่เรากำหนด Constraint ให้กับคอลัมน์ agency ของตาราง airport_list ในส่วนของ ON DELETE ให้เป็น ON CASCADE ทำให้เมื่อมีการลบข้อมูลใดในตาราง responsible_agency จะทำให้ข้อมูลนั้นในตาราง airport_list ถูกลบไปด้วย

หมายเหตุ:

นอกจากใช้เครื่องมือใน phpMyAdmin ในการกำหนด Constraint แล้ว เราสามารถกำหนด Constraint โดยการพิมพ์คำสั่ง SQL ได้ด้วยเช่นกัน

ข้อสำคัญ

ต้องกำหนด Primary Key, Foreign Key, Constraint ให้กับตารางต่าง ๆ ให้เสร็จเรียบร้อยก่อนที่จะทำการเพิ่มข้อมูลลงในตาราง

สรุป

• ก่อนที่จะทำการเชื่อมโยงข้อมูลระหว่างตาราง เราต้องเลือกตาราง 2 ตาราง (หรือมากกว่า) ที่มีคอลัมน์หนึ่งที่มีข้อมูลตรงกัน โดยคอลัมน์หนึ่งเป็นคอลัมน์ที่มีข้อมูลซ้ำกัน (คอลัมน์ที่เป็น Foreign Key) และอีกคอลัมน์หนึ่งเป็นคอลัมน์ที่มีข้อมูลไม่ซ้ำกัน (มักเป็นคอลัมน์ที่เป็น Primary Key)
• การกำหนด Constraint จะมี 2 ประเภทที่มีผลต่อการเปลี่ยนแปลงของข้อมูล คือ ON UPDATE และ ON DELETE ซึ่งทั้ง 2 ประเภทสามารถเลือกรูปแบบการเปลี่ยนแปลงข้อมูลได้อีก 4 รูปแบบ คือ ON CASCADE, SET NULL, NO ACTION, RESTRICT
• เมื่อกำหนด Constraint แล้ว การเพิ่ม แก้ไข หรือลบข้อมูลภายในตารางต่าง ๆ จะเปลี่ยนแปลงไปตามรูปแบบของ Constraint ที่เรากำหนด