SQLの予約テーブルを設計せよ

師範 / 目安 35分

貸出中の本を予約できるようにします。要件は次のとおりです。

誰がどの本を、いつ予約したかを残す。同じ人が同じ本を二重に予約することはできない。予約には「待機中」「確保済み」「取消」のいずれかの状態がある。

要件文のルールを制約に置き換えるのが今回の課題です。列の名前と型はひな形にあります。外部キー2つ、一意制約1つ、状態の CHECK 1つを、名前付きで書いてください。今回は状態の記録までを扱います。

完成条件

reservations テーブルを作ってください。

id integer PRIMARY KEY
book_id integer NOT NULL — books (id) を参照
member_id integer NOT NULL — members (id) を参照
reserved_on date NOT NULL
status text NOT NULL — '待機中' / '確保済み' / '取消' のいずれか

制約には名前を付けます。reservations_book_fk、reservations_member_fk、reservations_member_book_uk、reservations_status_ck の4つです。

ひな形には予約3件の INSERT と、仕上げの確認用 SQL もあります。確認用 SQL は information_schema.table_constraints から public スキーマの reservations の制約を constraint_type ごとに数えます。出す列は constraint_type と cnt、並びは constraint_type の昇順です。constraint_name が '%not_null' で終わるものは除きます。

名前付き制約の書き方

イベント申込の例です。events (id) と people (id) は既にあり、どちらも id が主キーだとします。

SQL クエリ

CREATE TABLE signups (
  id integer PRIMARY KEY,
  event_id integer NOT NULL,
  person_id integer NOT NULL,
  state text NOT NULL,
  CONSTRAINT signups_event_fk FOREIGN KEY (event_id) REFERENCES events (id),
  CONSTRAINT signups_person_fk FOREIGN KEY (person_id) REFERENCES people (id),
  CONSTRAINT signups_person_event_uk UNIQUE (person_id, event_id),
  CONSTRAINT signups_state_ck CHECK (state IN ('申込', '参加'))
);

PRIMARY KEY は行を識別し、重複と NULL を防ぎます。NOT NULL はその列の値を必須にします。CONSTRAINT 名前 の後ろがルールの本体です。FOREIGN KEY と REFERENCES は参照先に存在する番号だけを許します。UNIQUE は指定した列の組み合わせの重複を禁止します。CHECK は条件が満たされるかを検査し、IN は列の値がかっこの中のいずれかであることを表します。

この例なら、同じ人が同じイベントに2回申し込むことや、状態を '欠席' にすることは拒否されます。列定義と制約はカンマで区切り、閉じかっこの直前にはカンマを置きません。

information_schema.table_constraints はデータベースが用意する制約一覧です。作った申込テーブルの制約を確認する例を示します。

SQL クエリ

SELECT constraint_type, count(*) AS cnt
FROM information_schema.table_constraints
WHERE table_schema = 'public' AND table_name = 'signups'
  AND constraint_name NOT LIKE '%not_null'
GROUP BY constraint_type
ORDER BY constraint_type;

table_schema と table_name で対象のテーブルを選び、constraint_type ごとに数えます。NOT LIKE はパターンに一致しないものを残します。% は任意の文字列を表すので、ここでは名前が not_null で終わる制約を除いています。演習ではこの確認部分もひな形にあり、書き直す必要はありません。

進め方

ひな形の CREATE TABLE の中に、4つの名前付き制約を1行ずつ足してください。制約はカンマで区切り、最後の制約にはカンマを付けません。手が止まったらヒントを開けます。ヒントは3段階で、①方針、②使う構文、③部分解の順に出ます。1つも開けずに通せたら、その問題は自分のものです。

開かずに10分粘って進まないなら、①だけ開けてください。粘る時間そのものには価値がありません。価値があるのは、自分で書いた行が動いた瞬間です。

「二重に予約できない」は一意制約、「3つのいずれか」は CHECK です。どちらも要件文の日本語に対応が付きます。

constraint_type	cnt
CHECK	1
FOREIGN KEY	2
PRIMARY KEY	1
UNIQUE	1

師範 / 目安 35分

貸出中の本を予約できるようにします。要件は次のとおりです。

誰がどの本を、いつ予約したかを残す。同じ人が同じ本を二重に予約することはできない。予約には「待機中」「確保済み」「取消」のいずれかの状態がある。

完成条件

reservations テーブルを作ってください。

id integer PRIMARY KEY
book_id integer NOT NULL — books (id) を参照
member_id integer NOT NULL — members (id) を参照
reserved_on date NOT NULL
status text NOT NULL — '待機中' / '確保済み' / '取消' のいずれか

制約には名前を付けます。reservations_book_fk、reservations_member_fk、reservations_member_book_uk、reservations_status_ck の4つです。

名前付き制約の書き方

イベント申込の例です。events (id) と people (id) は既にあり、どちらも id が主キーだとします。

SQL クエリ

CREATE TABLE signups (
  id integer PRIMARY KEY,
  event_id integer NOT NULL,
  person_id integer NOT NULL,
  state text NOT NULL,
  CONSTRAINT signups_event_fk FOREIGN KEY (event_id) REFERENCES events (id),
  CONSTRAINT signups_person_fk FOREIGN KEY (person_id) REFERENCES people (id),
  CONSTRAINT signups_person_event_uk UNIQUE (person_id, event_id),
  CONSTRAINT signups_state_ck CHECK (state IN ('申込', '参加'))
);

information_schema.table_constraints はデータベースが用意する制約一覧です。作った申込テーブルの制約を確認する例を示します。

SQL クエリ

SELECT constraint_type, count(*) AS cnt
FROM information_schema.table_constraints
WHERE table_schema = 'public' AND table_name = 'signups'
  AND constraint_name NOT LIKE '%not_null'
GROUP BY constraint_type
ORDER BY constraint_type;

進め方

「二重に予約できない」は一意制約、「3つのいずれか」は CHECK です。どちらも要件文の日本語に対応が付きます。

予約テーブルを設計せよ

師範 / 目安 35分

完成条件

名前付き制約の書き方

進め方

テーブル構造

期待される出力

ヒント

予約テーブルを設計せよ

師範 / 目安 35分

完成条件

名前付き制約の書き方

進め方

テーブル構造

期待される出力

ヒント