เขียนเว็บ เดอะ ซีรีส์ ตอนที่ 33: คีย์นอกของตาราง (Foreign Key)
การออกแบบฐานข้อมูลในบางครั้งอาจจะต้องสร้างตารางมากกว่า 1 ตาราง ขึ้นอยู่กับระบบที่พัฒนาว่าต้องการเก็บข้อมูลจำนวนเท่าไร และมีรูปแบบในการจัดเก็บข้อมูลลงในฐานข้อมูลอย่างไร
การออกแบบฐานข้อมูลจะต้องวางแผนก่อนว่าควรจะมีกี่ตารางเพื่อใช้เก็บข้อมูล ซึ่งทุกตารางจะต้องมีคีย์หลักของตาราง (Primary Key) เพื่อใช้ในการบอกตำแหน่งของข้อมูล นอกจาก Primary Key แล้ว บางตารางอาจจะต้องมี คีย์นอกของตาราง หรือ Foreign Key เพราะในตารางอาจจะมีคอลัมน์ที่มีข้อมูลชุดเดียวกันกับคอลัมน์ของอีกตารางหนึ่ง ซึ่งเราสามารถใช้ Foreign Key ในการกำหนดเงื่อนไข หรือเรียกอีกอย่างว่า Constraint สำหรับการเชื่อมโยงข้อมูลระหว่างตารางได้
เริ่มต้นสร้างฐานข้อมูล
สำหรับตัวอย่างนี้จะสร้างฐานข้อมูลเพื่อใช้เก็บข้อมูลรายชื่อท่าอากาศยานในประเทศไทย ให้ตั้งชื่อฐานข้อมูลว่า airport_thai_db รองรับรหัสอักขระ utf8_unicode_ci โดยจะมีทั้งหมด 3 ตาราง ดังนี้
- airport_list เป็นตารางที่เก็บข้อมูลท่าอากาศยาน
- province เป็นตารางที่เก็บข้อมูลรายชื่อจังหวัดทั้ง 77 จังหวัด
- responsible_agency เป็นตารางที่เก็บข้อมูลรายชื่อหน่วยงานที่รับผิดชอบท่าอากาศยาน
ทุกคอลัมน์ในแต่ละตารางจะถูกกำหนดว่าต้องไม่มีค่า NULL (ระบุค่าไม่ได้) และทุกตารางจะต้องมีตัวที่กำหนดลักษณะการจัดเก็บข้อมูลโครงสร้างของตาราง หรือ Storage Engine เป็น InnoDB เพราะเป็น Storage Engine ที่รองรับการตั้งค่า Foreign Key (ดูวิธีการสร้างฐานข้อมูลและตารางในตอนที่ 27, 28, 29)
ออกแบบตาราง airport_list
ตาราง airport_list จะมีคอลัมน์และมีประเภทของข้อมูล (Data type) ในการเก็บข้อมูลดังนี้
- airport_no เก็บข้อมูลลำดับข้อมูล มี Data type เป็น Int โดยกำหนดให้เป็น Primary Key และตั้งค่าเป็น Auto Increment (ดูวิธีการกำหนด Primary Key และ Auto Increment)
- airport_iata เก็บข้อมูลรหัส 3 ตัวตามมาตรฐานของสมาคมขนส่งทางอากาศระหว่างประเทศ (IATA) มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 3
- airport_icao เก็บข้อมูลรหัส 4 ตัวตามมาตรฐานขององค์การการบินพลเรือนระหว่างประเทศ (ICAO) มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 4
- airport_name_th เก็บข้อมูลชื่อท่าอากาศยานภาษาไทย มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 100
- airport_name_en เก็บข้อมูลชื่อท่าอากาศยานภาษาอังกฤษ มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 100
- province เก็บข้อมูลรหัสจังหวัด มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 2
- agency เก็บข้อมูลรหัสหน่วยงานที่รับผิดชอบท่าอากาศยาน มี Data type เป็น Int มีความยาวในการเก็บข้อมูล คือ 1
ตั้งค่ากำหนดให้ Storage Engine (อยู่ก่อนปุ่ม Save) เป็น InnoDB (กรอบสีแดงตามภาพ)
เมื่อตั้งค่าเรียบร้อยแล้วให้คลิก Save จะได้โครงสร้างตารางดังภาพด้านล่าง
ออกแบบตาราง province
ตาราง province จะมีคอลัมน์และมี Data type ในการเก็บข้อมูลดังนี้
- province_code เก็บข้อมูลลำดับข้อมูลจังหวัด มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 2 และกำหนดให้เป็น Primary Key
- province_name เก็บข้อมูลชื่อจังหวัด มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 30
พร้อมทั้งตั้งค่ากำหนดให้ Storage Engine เป็น InnoDB
ออกแบบตาราง responsible_agency
ตาราง responsible_agency จะมีคอลัมน์และมี Data type ในการเก็บข้อมูลดังนี้
- agency_code เก็บข้อมูลลำดับข้อมูลหน่วยงานที่รับผิดชอบ มี Data type เป็น Int มีความยาวในการเก็บข้อมูล คือ 1 และกำหนดให้เป็น Primary Key
- agency_name เก็บข้อมูลชื่อหน่วยงานที่รับผิดชอบ มี Data type เป็น Varchar มีความยาวในการเก็บข้อมูล คือ 100
พร้อมทั้งตั้งค่ากำหนดให้ Storage Engine เป็น InnoDB
การกำหนด Foreign Key
ตาราง airport_list จะมีอยู่ 2 คอลัมน์ ที่มีการเก็บข้อมูลชุดเดียวกันกับตารางอื่น ๆ ได้แก่
- คอลัมน์ province เก็บข้อมูลรหัสจังหวัดเช่นเดียวกันกับคอลัมน์ province_code ในตาราง province
- คอลัมน์ agency เก็บข้อมูลรหัสหน่วยงานที่รับผิดชอบเช่นเดียวกันกับคอลัมน์ agency_code ในตาราง responsible_agency
ซึ่งทั้ง 2 คอลัมน์นี้ถือว่าเป็น Foreign Key ของตาราง airport_list และสามารถเชื่อมโยงข้อมูลกับตาราง province และตาราง responsible_agency ได้
เริ่มต้นไปที่หน้าตาราง airport_list และที่คอลัมน์ province จากนั้นคลิก More แล้วเลือก “Index” (กรอบสีแดงตามภาพ)
ระบบจะถามว่าต้องการเพิ่ม Foreign Key ให้กับคอลัมน์ province หรือไม่ ให้คลิก OK
เมื่อคลิก OK จะมีรูปกุญแจสีเทาอยู่ในคอลัมน์ (กรอบสีแดงตามภาพ)
และจะมีข้อมูล Foreign Key ของคอลัมน์ province อยู่ในหมวด Indexes (กรอบสีแดงตามภาพ)
สำหรับการกำหนด Foreign Key ในคอลัมน์ agency ให้ใช้วิธีเดียวกันกับการกำหนด Foreign Key ในคอลัมน์ province ซึ่งเมื่อกำหนด Foreign Key เรียบร้อยแล้วจะมีกุญแจสีเทาอยู่ในคอลัมน์ (กรอบสีแดงตามภาพ)
และจะมีข้อมูล Foreign Key ของคอลัมน์ agency อยู่ในหมวด Indexes (กรอบสีแดงตามภาพ)
ถ้าต้องการลบ Foreign Key ให้มาที่หมวด Indexes แล้วคลิก Drop บน Foreign Key ที่ต้องการลบ (กรอบสีแดงตามภาพ)
ระบบจะถามว่าต้องการลบ Foreign Key บนคอลัมน์ agency หรือไม่ ให้คลิก OK
เมื่อคลิก OK รูปกุญแจสีเทาในคอลัมน์ agency จะหายไป และข้อมูล Foreign Key ของคอลัมน์ agency ในหมวด Indexes ก็จะหายไปด้วย
ข้อมูลเพิ่มเติม:
- ในหมวด Indexes จะมี Keyname ของคีย์ต่าง ๆ อยู่ ถ้าเป็น Primary Key จะมี Keyname คือ PRIMARY ส่วน Foreign Key จะมี Keyname เป็นชื่อคอลัมน์
- การกำหนด Foreign Key ควรจะกำหนด 1 Foreign Key ต่อ 1 คอลัมน์
ถ้าเรารู้ตั้งแต่แรกแล้วว่าแต่ละตารางมีคอลัมน์ใดบ้างที่มีข้อมูลเหมือนกัน เราสามารถกำหนด Foreign Key ตั้งแต่เริ่มต้นสร้างตารางได้เลย โดยจะอยู่ในหมวด Index ให้เลือก INDEX เพื่อกำหนดให้คอลัมน์นั้นเป็น Foreign Key
การกำหนด Foreign Key ให้กับตารางแต่เพียงอย่างเดียวยังไม่มีผลใด ๆ ต่อการเพิ่ม ลบ หรือแก้ไขข้อมูลภายในตารางเพราะเรายังไม่ได้กำหนด Constraint เพื่อเชื่อมโยงข้อมูลระหว่างตารางซึ่งจะมีการอธิบายในตอนต่อไป
ในการสร้างฐานข้อมูล และสร้างตารางต่าง ๆ จะต้องกำหนด Primary Key และ Foreign Key ให้กับทุกตารางให้เสร็จเรียบร้อยก่อนที่จะทำการเพิ่ม ลบ หรือแก้ไขข้อมูล
สรุป
- นอกจากการกำหนด Primary Key ในตารางแล้ว เราสามารถกำหนดให้คอลัมน์ที่มีข้อมูลชุดเดียวกันกับคอลัมน์ของอีกตารางหนึ่งเป็นคียนอกของตาราง หรือ Foreign Key ได้
- การกำหนด Foreign Key เพียงอย่างเดียวไม่มีผลต่อการเพิ่ม ลบ หรือแก้ไขข้อมูลภายในตารางเพราะเราต้องกำหนดเงื่อนไข หรือ Constraint ก่อนเพื่อเชื่อมโยงข้อมูลระหว่างตาราง
- การกำหนด Foreign Key ในตารางควรจะกำหนด 1 Foreign Key ต่อ 1 คอลัมน์
- ควรจะกำหนด Primary Key และ Foreign Key ในตารางให้เรียบร้อยก่อนทำการเพิ่ม ลบ หรือแก้ไขข้อมูลในตารางทุกครั้ง