กลับไปที่คู่มือทั้งหมด
SQL··อ่าน 5 นาที

คีย์หลักกับคีย์นอก: รากฐานความสัมพันธ์ของฐานข้อมูล

ทำความเข้าใจคีย์หลักเทียบกับคีย์นอกใน SQL เรียนรู้วิธีที่คีย์เหล่านี้บังคับใช้ความสมบูรณ์ของข้อมูล สร้างความสัมพันธ์ และทำงานร่วมกับการดำเนินการแบบเรียงต่อกัน (cascading actions)

คีย์หลักของลูกค้า 42 ที่อ้างอิงโดยคีย์นอกของคำสั่งซื้อ พร้อมข้อจำกัด SQL ที่ชัดเจน
ในคู่มือนี้
  1. ทำความเข้าใจ Primary Keys
  2. ทำความเข้าใจ Foreign Keys
  3. คีย์หลักและคีย์นอกทำงานร่วมกันอย่างไร
  4. คีย์แบบคอมโพสิต
  5. การดำเนินการแบบ Cascading
  6. แนวปฏิบัติที่ดีที่สุด
  7. ข้อมูลอ้างอิงและการอ่านเพิ่มเติม

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

ทำความเข้าใจ Primary Keys

A คีย์หลัก (primary key) คือคอลัมน์ — หรือชุดของคอลัมน์ — ที่ระบุทุกแถวในตารางฐานข้อมูลได้อย่างเฉพาะเจาะจง ไม่มีสองแถวใดที่สามารถใช้ค่าคีย์หลักเดียวกันได้ และค่านั้นไม่สามารถเป็น NULL. ตารางสามารถประกาศคีย์หลักได้สูงสุดหนึ่งคีย์ และการเลือกคีย์หลักเป็นแนวทางปฏิบัติที่ดีสำหรับตารางแอปพลิเคชัน

คีย์ธรรมชาติ (Natural Keys) เทียบกับ คีย์ตัวแทน (Surrogate Keys)

A คีย์ธรรมชาติ (natural key) ใช้ข้อมูลที่มีอยู่แล้วในโดเมนโลกแห่งความเป็นจริง ตัวอย่างเช่น รหัส ISO ของประเทศ (US, FR) หรือ ISBN ของหนังสือ คีย์ธรรมชาติมีความหมายแต่สามารถเปลี่ยนแปลงได้เมื่อเวลาผ่านไปและอาจมีข้อกังวลด้านความเป็นส่วนตัว (ลองนึกถึงหมายเลขประกันสังคม)

A คีย์ตัวแทน (surrogate key) คือตัวระบุเทียมที่ไม่มีความหมายทางธุรกิจ — โดยทั่วไปจะเป็นจำนวนเต็มที่เพิ่มขึ้นอัตโนมัติหรือ UUID คีย์ตัวแทนมีความเสถียร กะทัดรัด และจัดทำดัชนีได้ง่าย ซึ่งเป็นเหตุผลว่าทำไมสคีมาสมัยใหม่ส่วนใหญ่จึงนิยมใช้คีย์เหล่านี้

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

ทำความเข้าใจ Foreign Keys

A คีย์นอก คือคอลัมน์ (หรือกลุ่มของคอลัมน์) ในตารางหนึ่งที่อ้างอิงถึงคีย์หลักหรือคีย์เฉพาะอื่นที่มีสิทธิ์ รวมถึงคีย์ในตารางเดียวกัน มันบังคับใช้ ความสมบูรณ์ของการอ้างอิง (referential integrity): ค่าคีย์นอกที่ไม่ใช่ null ต้องมีแถวที่อ้างอิงที่ตรงกัน คีย์นอกที่ยอมรับ null ได้และแบบคอมโพสิตจำเป็นต้องมีกฎการยอมรับ null และการจับคู่ที่ชัดเจน

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

เมื่อมีข้อจำกัดนี้อยู่ ฐานข้อมูลจะปฏิเสธทุกอย่างที่ INSERT เข้าไปใน posts ที่ซึ่ง author_id ไม่ตรงกับที่มีอยู่ users.id.

ข้อจำกัด Foreign key ที่แสดงเป็นเส้นความสัมพันธ์ใน ERD
คีย์หลักระบุแถว; คีย์นอกเปลี่ยนตัวระบุเหล่านั้นให้เป็นความสัมพันธ์ที่มองเห็นได้ในแผนภาพ

คีย์หลักและคีย์นอกทำงานร่วมกันอย่างไร

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

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

ในการร่างเอนทิตีก่อนเขียน SQL ให้เปิด ตัวสร้าง ERD. หากต้องการตรวจสอบตารางและคำจำกัดความของคีย์นอกข้างต้น ให้วางลงใน ตัวแก้ไข SQL เป็น ERD.

คีย์แบบคอมโพสิต

บางครั้งคอลัมน์เดียวไม่เพียงพอที่จะรับประกันความเป็นเอกลักษณ์ คีย์ผสม (เรียกอีกอย่างว่า คีย์ประกอบ) รวมคอลัมน์ตั้งแต่สองคอลัมน์ขึ้นไปไว้ในคีย์หลักเดียว รูปแบบนี้เป็นเรื่องปกติใน ตารางเชื่อม (junction tables) (หรือที่รู้จักกันในชื่อตารางเชื่อมหรือตารางเชื่อมโยง) ที่ใช้สำหรับความสัมพันธ์แบบกลุ่มต่อกลุ่ม

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

คีย์หลักแบบผสม (student_id, course_id) รับประกันว่านักเรียนคนเดียวกันไม่สามารถลงทะเบียนเรียนในวิชาเดิมซ้ำได้ ในขณะที่ยังคงอนุญาตให้นักเรียนแต่ละคนเรียนได้หลายวิชาและแต่ละวิชาสามารถมีนักเรียนได้หลายคน

การดำเนินการแบบ Cascading

เมื่อคุณกำหนดคีย์นอก (foreign key) คุณยังสามารถระบุได้ว่าจะเกิดอะไรขึ้นเมื่อแถวที่ถูกอ้างอิงถูกอัปเดตหรือลบ สิ่งเหล่านี้ การดำเนินการอ้างอิง (referential actions) รักษาข้อมูลของคุณให้สอดคล้องกันโดยไม่ต้องล้างข้อมูลด้วยตนเอง

  • CASCADE — ลบหรืออัปเดตแถวลูกโดยอัตโนมัติเมื่อแถวแม่มีการเปลี่ยนแปลง
  • SET NULL — ตั้งค่าคอลัมน์คีย์นอกเป็น NULL (คอลัมน์ต้องอนุญาตให้เป็นค่าว่างได้ (nullable)).
  • SET DEFAULT — รีเซ็ตคอลัมน์เป็นค่าเริ่มต้น ซึ่งยังคงต้องเป็นไปตามข้อจำกัดของคีย์นอก การรองรับจะแตกต่างกันไปตามเอนจิน; MySQL InnoDB ปฏิเสธการดำเนินการนี้
  • RESTRICT / NO ACTION — ปฏิเสธการเปลี่ยนแปลงที่ทิ้งการอ้างอิงที่ไม่ถูกต้องไว้ ใน PostgreSQL, NO ACTION อาจถูกเลื่อนออกไปได้เมื่อข้อจำกัดสามารถเลื่อนได้ (deferrable); ส่วน RESTRICT จะมีผลทันที
CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

ใช้ CASCADE อย่างระมัดระวัง — การลบแถวหลักจะลบแถวลูกที่เกี่ยวข้องทั้งหมดโดยไม่มีการแจ้งเตือน ในระบบที่มีการตรวจสอบ (audit) สูง ให้พิจารณา RESTRICT รวมกับแฟล็กการลบแบบซอฟต์ (soft-delete) แทน

แนวปฏิบัติที่ดีที่สุด

  • เลือกใช้ คีย์ตัวแทน (SERIAL, BIGSERIAL, หรือ UUID) เป็นคีย์หลักสำหรับตารางแอปพลิเคชัน ใช้คีย์ธรรมชาติเมื่อตัวระบุโดเมนมีความเสถียรเพียงพอสำหรับความต้องการของคุณ และวางแผนว่าการเปลี่ยนแปลงจะส่งผลต่อการอ้างอิงอย่างไร
  • เสมอ ตั้งชื่อข้อกำหนดเงื่อนไข (constraints) ของคุณ (เช่น, fk_posts_author) เพื่อให้ข้อความแสดงข้อผิดพลาดมีความหมาย
  • คอลัมน์คีย์นอกของดัชนี. ฐานข้อมูลจำนวนมากไม่สร้างดัชนีบนคีย์นอกโดยอัตโนมัติ และการขาดดัชนีอาจทำให้การเชื่อมต่อและการลบแบบต่อเนื่องช้าลงอย่างมาก
  • คิดเกี่ยวกับ การดำเนินการแบบต่อเนื่อง (cascading action) สำหรับคีย์นอกทุกตัว การตั้งค่าเริ่มต้นเป็น NO ACTION ปลอดภัยที่สุด; เปลี่ยนไปใช้ CASCADE เฉพาะเมื่อแถวลูกสูญเสียความหมายอย่างแท้จริงหากไม่มีแถวแม่
  • เลือกคีย์ผสมจากกฎเอกลักษณ์ของแบบจำลอง คีย์ตัวแทนสามารถทำให้การอ้างอิงง่ายขึ้น แต่ให้คงข้อจำกัด UNIQUE ไว้ที่คีย์ธุรกิจเดิมเมื่อจำเป็นต้องป้องกันข้อมูลซ้ำ
  • สร้างแบบจำลองสคีมาของคุณด้วยภาพก่อน ตัวสร้าง ERD ช่วยให้คุณร่างแบบจำลองเชิงแนวคิดและตรวจสอบการแปลงเป็น SQL ได้ ซึ่งช่วยตรวจจับข้อผิดพลาดในการออกแบบก่อนที่จะนำไปใช้งานจริง

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

โพสต์เพิ่มเติม