คีย์นอก (FOREIGN KEY) ใน SQL Server: วิธีการสร้างพร้อมตัวอย่าง

คีย์ต่างประเทศคืออะไร?

คีย์ต่างประเทศเป็นวิธีการหนึ่งในการบังคับใช้ความสมบูรณ์ของการอ้างอิงภายใน SQL Serverกล่าวโดยง่าย คีย์ต่างประเทศ (Foreign Key) ช่วยให้มั่นใจได้ว่าค่าในตารางหนึ่งจะต้องมีอยู่ในอีกตารางหนึ่ง

กฎสำหรับคีย์ต่างประเทศ

• ค่า NULL สามารถใช้ได้ในคีย์ต่างประเทศของ SQL
• ตารางที่ถูกอ้างถึงเรียกว่าตารางหลัก (parent table)
• ตารางที่มีคีย์ต่างประเทศเรียกว่าตารางลูก
• คีย์ต่างประเทศในตารางลูกอ้างอิงถึง คีย์หลัก ในตารางหลัก
• ความสัมพันธ์ระหว่างพ่อแม่และลูกนี้บังคับใช้กฎที่เรียกว่า “ความสมบูรณ์ของการอ้างอิง”

แผนภาพด้านล่างสรุปประเด็นทั้งหมดข้างต้นเกี่ยวกับคีย์ต่างประเทศ

วิธีสร้างคีย์ต่างประเทศใน SQL

คุณสามารถสร้าง Foreign Key ใน SQL Server ได้สองวิธี:

• สตูดิโอจัดการเซิร์ฟเวอร์ SQL
• T-SQL

สตูดิโอจัดการเซิร์ฟเวอร์ SQL

ตารางหลัก: สมมติว่าเรามีตารางหลักอยู่แล้วชื่อ 'Course' โดยมีคอลัมน์ Course_ID และ Course_name และ Course_ID เป็นคีย์หลัก

ตารางลูก: เราจำเป็นต้องสร้างตารางที่สองเป็นตารางลูก โดยมี 'Course_ID' และ 'Course_Strength' เป็นสองคอลัมน์ แต่ 'Course_ID' จะต้องเป็นคีย์นอก (foreign key)

ขั้นตอนที่ 1) คลิกขวาที่ ตาราง > สร้างใหม่ > ตาราง…

ขั้นตอนที่ 2) ป้อนชื่อคอลัมน์สองชื่อ ได้แก่ 'Course_ID' และ 'Course_Strength' คลิกขวาที่คอลัมน์ 'Course_Id' แล้วคลิก ความสัมพันธ์ (Relationship)

ขั้นตอนที่ 3) ในส่วน 'ความสัมพันธ์คีย์ต่างประเทศ' ให้คลิก 'เพิ่ม'

ขั้นตอนที่ 4) ใน 'การกำหนดค่าตารางและคอลัมน์' ให้คลิกที่ไอคอน '...'

ขั้นตอนที่ 5) เลือก 'ตารางคีย์หลัก' เป็น 'COURSE' และเลือกตารางใหม่ที่จะสร้างเป็น 'ตารางคีย์รอง' จากเมนูแบบดรอปดาวน์

ขั้นตอนที่ 6) สำหรับ 'ตารางคีย์หลัก' ให้เลือกคอลัมน์ 'Course_Id' เป็นคอลัมน์คีย์หลักของตาราง

สำหรับ 'ตารางคีย์ต่างประเทศ' ให้เลือกคอลัมน์ 'Course_Id' เป็นคอลัมน์คีย์ต่างประเทศ จากนั้นคลิก ตกลง

ขั้นตอนที่ 7) คลิกที่ปุ่ม เพิ่ม

ขั้นตอนที่ 8) ตั้งชื่อตารางว่า 'Course_Strength' แล้วคลิก ตกลง

ผลลัพธ์: เราได้สร้างความสัมพันธ์แบบพ่อ-ลูกระหว่าง 'หลักสูตร' และ 'ระดับความยากของหลักสูตร'

T-SQL: สร้างตารางความสัมพันธ์แบบพ่อ-ลูกโดยใช้ T-SQL

ตารางหลัก: ลองพิจารณาอีกครั้งว่าเรามีตารางหลักอยู่แล้วชื่อ 'Course' โดยมี Course_ID และ Course_name เป็นสองคอลัมน์ และ Course_ID เป็นคีย์หลัก

ตารางลูก: เราจำเป็นต้องสร้างตารางที่สองเป็นตารางลูก โดยตั้งชื่อว่า 'Course_Strength_TSQL' ตารางนี้จะมี 2 คอลัมน์ คือ 'Course_ID' และ 'Course_Strength' แต่ 'Course_ID' จะต้องเป็นคีย์นอก (foreign key)

ด้านล่างนี้คือไวยากรณ์สำหรับ สร้างตาราง โดยใช้ FOREIGN KEY

ไวยากรณ์:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

นี่คือคำอธิบายของพารามิเตอร์ข้างต้น:

• childTable คือชื่อของตารางที่จะสร้าง
• column_1 และ column_2 คือคอลัมน์ที่จะเพิ่มเข้าไปในตาราง
• fkey_name คือชื่อของข้อจำกัดคีย์ต่างประเทศที่จะสร้างขึ้น
• child_column1, child_column2 … child_column_n คือคอลัมน์ในตารางลูกที่อ้างอิงถึงคีย์หลักในตารางแม่
• parentTable คือชื่อของตารางหลักที่มีคีย์ถูกอ้างอิงในตารางย่อย
• parent_column1, parent_column2 … parent_column_n คือคอลัมน์ที่ประกอบกันเป็นคีย์หลักของตารางแม่
• ON DELETE เป็นพารามิเตอร์เสริมที่ระบุว่าจะเกิดอะไรขึ้นกับข้อมูลลูกหลังจากที่ข้อมูลหลักถูกลบ ค่าที่ใช้ได้ ได้แก่ NO ACTION, SET NULL, CASCADE หรือ SET DEFAULT
• ON UPDATE เป็นพารามิเตอร์เสริมที่ระบุว่าจะเกิดอะไรขึ้นกับข้อมูลลูกหลังจากที่ข้อมูลหลักได้รับการอัปเดต ค่าที่ใช้ได้ ได้แก่ NO ACTION, SET NULL, CASCADE หรือ SET DEFAULT
• การตั้งค่า "ไม่มีการดำเนินการ" หมายความว่า ข้อมูลของลูกจะไม่มีการเปลี่ยนแปลงใดๆ หลังจากที่ข้อมูลของแม่ได้รับการอัปเดตหรือลบแล้ว
• CASCADE หมายถึง ข้อมูลของลูกจะถูกลบหรืออัปเดตหลังจากที่ข้อมูลของแม่ถูกลบหรืออัปเดตแล้ว
• SET NULL หมายความว่าข้อมูลของลูกจะถูกตั้งค่าเป็นค่าว่าง (null) หลังจากที่ข้อมูลของแม่ได้รับการอัปเดตหรือลบออก
• SET DEFAULT หมายความว่าข้อมูลของลูกจะถูกตั้งค่าเป็นค่าเริ่มต้นหลังจากมีการอัปเดตหรือลบข้อมูลของแม่

เรามาดูตัวอย่างคีย์ต่างประเทศที่สร้างตารางที่มีคอลัมน์เดียวเป็นคีย์ต่างประเทศ โดยใช้ ประเภทข้อมูล สำหรับแต่ละคอลัมน์

ตัวอย่างการใช้ Foreign Key ใน SQL

ค้นหา:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

ขั้นตอนที่ 1) เรียกใช้คำสั่งค้นหาโดยคลิกที่ปุ่ม "เรียกใช้" (Execute)

ผลลัพธ์: เราได้สร้างความสัมพันธ์แบบพ่อ-ลูกระหว่าง 'Course' และ 'Course_Strength_TSQL' แล้ว

การใช้ ALTER TABLE

ต่อไปนี้เราจะเรียนรู้วิธีการเพิ่มคีย์ต่างประเทศใน SQL Server ลงในตารางที่มีอยู่แล้วโดยใช้คำสั่ง ALTER TABLE เราจะใช้ไวยากรณ์ที่แสดงด้านล่างนี้:

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

นี่คือคำอธิบายของพารามิเตอร์ที่ใช้ด้านบน:

• childTable คือชื่อของตารางที่จะสร้าง
• column_1 และ column_2 คือคอลัมน์ที่จะเพิ่มเข้าไปในตาราง
• fkey_name คือชื่อของข้อจำกัดคีย์ต่างประเทศที่จะสร้างขึ้น
• child_column1, child_column2 … child_column_n คือคอลัมน์ในตารางลูกที่อ้างอิงถึงคีย์หลักในตารางแม่
• parentTable คือชื่อของตารางหลักที่มีคีย์ถูกอ้างอิงในตารางย่อย
• parent_column1, parent_column2 … parent_column_n คือคอลัมน์ที่ประกอบกันเป็นคีย์หลักของตารางแม่

ตัวอย่างคำสั่ง ALTER TABLE add foreign key:

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

เราได้สร้างคีย์ต่างประเทศชื่อ fkey_student_admission บนตารางแผนก คีย์ต่างประเทศนี้อ้างอิงถึงคอลัมน์การรับเข้าของตารางนักเรียน

ตัวอย่างแบบสอบถามคีย์ต่างประเทศ

ขั้นแรก เรามาดูข้อมูลในตารางหลักของเรากันก่อน นั่นก็คือตาราง COURSE

ค้นหา:

SELECT * from COURSE;

ต่อไปเราจะแทรกข้อมูลลงในตารางย่อย 'Course_Strength_TSQL' โดยเราจะลองแทรกข้อมูลสองประเภท:

• ประเภทแรก ซึ่ง Course_Id ในตารางลูกมีอยู่ใน Course_Id ของตารางแม่ กล่าวคือ Course_Id = 1 และ 2
• ประเภทที่สอง ซึ่ง Course_Id ในตารางลูกไม่ตรงกับ Course_Id ในตารางแม่ กล่าวคือ Course_Id = 5

ค้นหา:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

ผลลัพธ์: มาลองรันคำสั่งค้นหาพร้อมกันเพื่อดูตารางหลักและตารางย่อยของเรากัน

แถวที่มี Course_ID 1 และ 2 มีอยู่ในตาราง Course_Strength อย่างไรก็ตาม Course_ID 5 เป็นข้อยกเว้น เนื่องจากไม่มีแถวที่ตรงกันในตารางหลัก

คำถามที่พบบ่อย