คีย์นอกและความสมบูรณ์เชิงอ้างอิง

รับประกันความสอดคล้องระหว่างตารางของคุณด้วย REFERENCES และเลือกพฤติกรรม ON DELETE อย่างจงใจ

8 นาที · 3 คำถาม

คีย์นอก (foreign key) ประกาศว่าคอลัมน์หนึ่งชี้ไปยังคีย์หลักของอีกตารางหนึ่ง จากนั้นฐานข้อมูลจะปฏิเสธค่ากำพร้าใด ๆ คุณไม่สามารถสร้างหนังสือที่ผู้เขียนไม่มีตัวตนได้ ระวังไว้ ใน SQLite การตรวจสอบนี้ ถูกปิดไว้โดยค่าเริ่มต้น จงเปิดใช้งานมันด้วย PRAGMA ที่ส่วนบนสุดของสคริปต์ของคุณ

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
    REFERENCES authors(id)
    ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Ted Chiang');

INSERT INTO books (id, title, author_id) VALUES
  (1, 'A Wizard of Earthsea', 1),
  (2, 'The Dispossessed', 1),
  (3, 'Exhalation', 2);

ทดสอบการปกป้องนี้ ลองแทรกหนังสือกำพร้า แล้วลบผู้เขียนคนหนึ่ง คำสั่งแรกจะถูกปฏิเสธ ส่วนคำสั่งที่สองจะพาหนังสือของมันไปด้วยด้วยอานิสงส์ของ ON DELETE CASCADE

INSERT INTO books (id, title, author_id)
VALUES (4, 'Orphan', 99);

DELETE FROM authors WHERE id = 1;

SELECT id, title FROM books;

ON DELETE กำหนดชะตากรรมของแถวลูกเมื่อแถวแม่หายไป CASCADE จะลบพวกมันไปพร้อมกัน SET NULL เก็บแถวไว้แต่ล้างการอ้างอิง ดังนั้นคอลัมน์ต้องยอมรับ NULL ได้ หากไม่มีประโยคใดกำกับ SQLite จะใช้ NO ACTION นั่นคือการลบแถวแม่จะถูกปฏิเสธตราบเท่าที่ยังมีลูกเหลืออยู่ ซึ่งเป็นพฤติกรรมที่ใกล้เคียงกับ RESTRICT

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  reviewer_id INTEGER
    REFERENCES authors(id)
    ON DELETE SET NULL
);

ทดสอบความรู้

ตรวจสอบว่าคุณจำประเด็นสำคัญของบทเรียนนี้ได้ครบถ้วน

1. เมื่อมี ON DELETE CASCADE บน books.author_id คำสั่ง DELETE FROM authors WHERE id = 1 จะทำให้เกิดอะไร?
   • เกิดข้อผิดพลาดตราบเท่าที่ผู้เขียนยังมีหนังสือ
   • การลบผู้เขียนและหนังสือทั้งหมดของพวกเขา
   • การตั้ง author_id ในหนังสือของพวกเขาให้เป็น NULL
2. ทำไมจึงต้องรัน PRAGMA foreign_keys = ON; ใน SQLite?
   • เพื่อสร้างคีย์นอก
   • เพราะการตรวจสอบคีย์นอกถูกปิดไว้โดยค่าเริ่มต้น เป็นรายการเชื่อมต่อไป
   • เพื่อให้การเชื่อมตารางทำงานเร็วขึ้น
3. คอลัมน์ reviewer_id ต้องมีเงื่อนไขใดจึงจะใช้ ON DELETE SET NULL ได้?
   • ถูกประกาศเป็น UNIQUE
   • เป็นชนิด INTEGER
   • ยอมรับ NULL ได้ จึงต้องไม่มี NOT NULL