0コメント

AP試験データベース分野：SQL応用と参照制約（CASCADE/RESTRICT）の仕組みを完全理解

【応用情報技術者】SQLマスターガイド：WITH句・CASE式から参照制約の神髄まで徹底解説

🚀 応用情報技術者試験 SQL完全攻略｜WITH句・CASE式から参照制約まで徹底解説

単なる暗記ではなく、実務でも役立つ「なぜこの構文を使うのか」を徹底解剖します。

🔍 午後試験の重要トレンド：SQL応用とデータ整合性

応用情報技術者試験の午後問題において、データベース分野は「得点源」と言われますが、近年の傾向として単純なSELECT文だけでなく、データの「一貫性」や「保守性」を問う問題が増加しています。

📍 午後試験での出題傾向
  • WITH句による可読性向上： 複雑なビジネスロジックを一時表として整理させる。
  • CASE式によるデータクレンジング： 不備のあるデータを更新・集計する際、SQL内で分岐処理を行う。
  • 参照動作の挙動分析： テーブル削除時の連鎖（CASCADE）が業務に与える影響を文章で説明させる。

🎓 本記事でマスターする重要用語

共通表式(CTE) 参照制約 CASCADE / RESTRICT ドメイン制約 探索CASE式

※近年のAP午後試験では、ER図から制約を読み取り、適切なSQLを記述する能力が合否を分けます。

1. 📊 複雑な集計をスマートにする「WITH句」

【概要】共通表式（CTE）

複雑なサブクエリに名前をつけて、一時的な表として定義する仕組みです。コードの可読性が飛躍的に向上します。

📍 具体的な使用状況

  • 大規模データの多段集計： 「月ごとの売上合計」を出し、その中から「平均以上の月」を抽出するなど、同じ集計結果を2回以上参照する場合。
  • 再帰クエリ： 組織図や部品構成（BOM）など、階層構造を探索する場合。
試験のツボ： WITH句で定義した表は、そのクエリ内でのみ有効な「使い捨てのビュー」のようなイメージです。

2. ⚖️ SQL内の条件分岐「CASE式」

【概要】値の変換とカテゴリ化

「もしAなら1、Bなら2」という処理をSQL上で完結させます。SELECTだけでなく、ORDER BYやUPDATEでも大活躍します。

📍 具体的な使用状況

  • スコアリング： テストの点数を「優・良・可」に変換して集計する。
  • フラグの反転： 0なら1、1なら0へ更新するといった、トグル処理のUPDATE。

3. 🔄 データの更新三銃士（INSERT / UPDATE / DELETE）

【概要】DMLによるデータ操作

基本操作ですが、応用情報では「サブクエリとの組み合わせ」が頻出です。

操作 重要ポイント（APレベル）
INSERTSELECT文の結果をまるごと挿入できる。※挿入先の表をSELECTで使うのはNG。
UPDATECASE式を使い、条件によって更新値を変える「一括更新」が効率的。
DELETEWHEREを忘れると全削除。表自体を消すのはDROP、全行高速削除はTRUNCATE。

4. 🛡️ データベースの砦「参照制約と参照動作」

データの矛盾を防ぐ「整合性」は、AP試験で最も狙われる分野です。

【重要】参照動作（Referential Actions）

親（被参照表）が消えたり変わったりしたとき、子（参照表）はどう振る舞うべきか？

  • 💎 CASCADE（連鎖）： 親の削除に合わせて子も削除。実務では「退会したユーザーの投稿をすべて消す」などの処理に使用。
  • ⛔ RESTRICT / NO ACTION（拒否）： 子がいるなら親は消せな。実務では「在庫がある商品のマスタ削除を禁止する」などの安全策に使用。
  • ⚪ SET NULL： 親が消えたら、子の該当列をNULLにする。「担当者が辞めたが、案件データは残し、担当者欄を空にする」場合に使用。

💻 実践：参照動作のSQL記述例

外部キーを設定する際の CREATE TABLE 文において、参照制約の後に動作を指定します。

🔗 1. CASCADE（連鎖削除・更新）の設定

親テーブルの「部門」が削除されたら、所属する「社員」も自動的に削除する設定です。

CREATE TABLE 社員 ( 社員番号 CHAR(5) PRIMARY KEY, 氏名 VARCHAR(20), 部門コード CHAR(3), FOREIGN KEY (部門コード) REFERENCES 部門 (部門コード) ON DELETE CASCADE  -- 親の削除に連鎖 ON UPDATE CASCADE  -- 親の更新に連鎖 );

🚫 2. RESTRICT / NO ACTION（削除拒否）の設定

社員が一人でも所属している部門は、削除できないようにブロックする（安全重視）設定です。

CREATE TABLE 社員 ( 社員番号 CHAR(5) PRIMARY KEY, 部門コード CHAR(3), FOREIGN KEY (部門コード) REFERENCES 部門 (部門コード) ON DELETE RESTRICT  -- 所属社員がいる場合は親を削除不可 );
💡 試験対策Point:
実務では、誤操作によるデータ喪失を防ぐために RESTRICT がデフォルトで使われることが多いですが、試験では「連鎖して削除された」という状況設定で CASCADE の挙動を問う問題が頻出です。

📝 午後試験形式：実力診断ミニ演習

【シナリオ】
ECサイトのデータベース管理において、特定ランク（'S'）の顧客に期間限定の特別割引（一律5,000円引き）を適用した「注文履歴」の一覧を作成したい。 ただし、割引後の価格が0円以下になる場合は、一律100円とする。

問：次のSQL文の空欄（ア）〜（ウ）を埋めなさい。

WITH 特別顧客 AS ( SELECT 顧客ID FROM 顧客マスタ WHERE ランク = 'S' ) SELECT J.注文番号, CASE WHEN J.金額 - 5000 <= 0 THEN 100 ELSE (（ ア ）) END AS 決済金額 FROM 注文履歴 J WHERE J.顧客ID （ イ ） (SELECT 顧客ID FROM （ ウ ）);
解答を表示する

【正解】

  • （ ア ）： J.金額 - 5000
  • （ イ ）： IN （または = ANY）
  • （ ウ ）： 特別顧客

【解説】

（ア） CASE式において、条件に合致しない場合の「通常の計算式」を記述します。
（イ）（ウ） WITH句で定義した共通表式（特別顧客）を副問合せの中で参照します。特定の集合に含まれるか判定するため IN を使用します。

✍️ 過去問レベル：午前・午後 複合演習問題

【問題 1】参照制約の動作（午前形式）

「商品マスタ」テーブルを親、「在庫」テーブルを子とする参照制約において、ON DELETE CASCADE を指定した。このとき、商品マスタから特定の商品行を削除した場合の挙動として正しいものはどれか。

  • ア：在庫テーブルに関連する行がある場合、削除が拒否される。
  • イ：商品マスタの行は削除され、在庫テーブルの関連する行は自動的に削除される。
  • ウ：商品マスタの行は削除され、在庫テーブルの外部キーにはNULLが設定される。
  • エ：商品マスタの行は削除されるが、在庫テーブルには何の影響も与えない。

【問題 2】CASE式を用いた一括更新（午後形式）

販売キャンペーンに伴い、商品テーブルの「単価」を以下のルールで一括更新したい。空欄（ A ）に入る適切なSQLを答えなさい。

【更新ルール】
・カテゴリが '文具' の場合は10%引き
・カテゴリが '家電' の場合は20%引き
・それ以外は価格を据え置く 
UPDATE 商品 SET 単価 = CASE カテゴリ WHEN '文具' THEN 単価 * 0.9 WHEN '家電' THEN 単価 * 0.8 （ A ） END;
解答と解説を確認する

【解答】

問題 1： イ
解説：CASCADE（カスケード）は「連鎖」を意味します。親の削除に合わせて子も自動的に消去される設定です。

問題 2： ELSE 単価
解説：CASE式で ELSE を省略し、どの条件にも合致しない場合、その値は NULL になってしまいます。価格を据え置く（元の値のままにする）には、ELSE 単価 と記述する必要があります。

5. 📋 整合性を保つ「4つの制約」まとめ

データの品質を担保するためのルールです。

  1. 一意性制約： 主キーなど、値のダブりを許さない。
  2. 参照制約： 外部キーが親テーブルに実在することを保証する。
  3. 形式制約： データ型や桁数が正しいこと。
  4. ドメイン制約： 「0〜100の間」など、値の範囲が正しいこと。

📝 まとめ：合格へのチェックリスト

  • ✅ WITH句は「複雑な副問合せへの名前付け」と覚えよ！
  • ✅ CASE式は「値が使える場所ならどこでも書ける」のが強み！
  • ✅ CASCADEは「道連れ」、RESTRICTは「親を守る」とイメージせよ！
  • ✅ DELETEは行削除、DROPは表削除。混同は厳禁！

💡 おわりに：試験の先にある「生きたデータベース設計」

ここまで、WITH句やCASE式といった応用的なSQL構文から、データの整合性を守る参照制約までを解説してきました。応用情報技術者試験において、データベース分野は「一度理解すれば得点が安定する」非常にコストパフォーマンスの高い分野です。

🌐 周辺知識とさらなるステップ

試験対策を終えたら、ぜひ以下の「周辺知識」にも目を向けてみてください。

  • ストアドプロシージャ： SQLを一連の手続きとしてサーバー側に保存する技術。
  • インデックス設計： 大規模データでCASE式やJOINを高速化するための必須技術。
  • 正規化理論： なぜ「参照制約」が必要になるのか、その根本となるテーブル分割のルール。

午後の記述問題で「CASCADE」や「RESTRICT」の挙動を問われた際、それは単なる用語の暗記ではなく、「もしこのシステムでデータが不整合を起こしたら、ビジネスにどんな損害が出るか？」という視点を持つことが合格、そして実務への近道です。

あなたの合格を心より応援しています！次は「正規化理論」でお会いしましょう。✨

📖 重要用語マスターリスト（網羅的解説）

■ WITH句（共通表式 / CTE）
クエリ内で一時的な結果セットに名前を付け、再利用可能にする構文。複雑なサブクエリを整理し、可読性と保守性を高めるために使用されます。
■ CASE式
条件に応じて値を出し分ける「条件分岐」の式。SELECT文での値変換や、UPDATE文での条件付き更新に必須の知識です。
■ INSERT文
新しい行を表に追加するコマンド。VALUESによる直接入力のほか、SELECT結果を挿入する形式も頻出です。
■ UPDATE文
既存のデータを書き換えるコマンド。WHERE句を指定しないと全行が更新されるため、実務・試験共に注意が必要です。
■ DELETE文
表から行を削除するコマンド。表の構造自体を消すDROP文や、高速に全行消去するTRUNCATE文との違いが問われます。
■ 参照制約（外部キー制約）
関連する2つの表の間で、データの矛盾を防ぐためのルール。子テーブルの外部キーの値が、親テーブルの主キーに必ず存在することを保証します。
■ CASCADE（カスケード）
「連鎖」を意味する参照動作。親テーブルの行を削除・更新した際、関連する子テーブルの行も自動的に削除・更新されます。
■ RESTRICT / NO ACTION
参照整合性を維持するため、子テーブルに関連行がある場合に親テーブルの削除・更新を拒否（禁止）する動作です。
■ SET NULL / SET DEFAULT
親テーブルの行が削除された際、子テーブルの外部キーの値を、それぞれNULL（空）または既定値に書き換える動作です。
■ 一意性制約
列内の値が重複することを禁止する制約。主キー（PRIMARY KEY）には必ずこの制約が含まれます。
■ ドメイン制約（CHECK制約）
データが取り得る値の範囲（ドメイン）を制限する制約。「0以上100以下」といった値の妥当性を検証します。
■ 形式制約
データのデータ型（数値型、文字型など）や桁数が定義通りであることを保証する制約です。

この記事があなたの合格の助けになれば幸いです！🍀

この記事へのコメント

コメントを書く