外部キーと参照整合性

REFERENCES でテーブル間の一貫性を保証し、ON DELETE の動作を意図的に選びましょう。

8 分 · 3 問

外部キーは、ある列が別のテーブルの主キーを指すことを宣言します。するとデータベースは、みなしごの値をすべて拒みます。著者が存在しない本を作ることはできません。注意してください。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. books.author_id に ON DELETE CASCADE があるとき、DELETE FROM authors WHERE id = 1 は何を引き起こしますか？
   • 著者に本がある限りエラーになる
   • 著者と、そのすべての本の削除
   • その本の author_id をNULLにする
2. SQLiteで PRAGMA foreign_keys = ON; を実行するのはなぜですか？
   • 外部キーを作成するため
   • 外部キーのチェックが、接続ごとにデフォルトで無効になっているため
   • 結合を速くするため
3. ON DELETE SET NULL を使うために、reviewer_id 列が満たさなければならない条件は何ですか？
   • UNIQUE として宣言されている
   • INTEGER 型である
   • NULLを受け入れる、つまり NOT NULL を持たない