9. 主キー，参照整合性制約，従属

9. 主キー，参照，参照整合性制約 URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞：この資料では「いらすとや」のイラストを使用しています 1

• https://www.kkaneko.jp/de/ds/index.html

全体まとめ • 主キー 主キーは，通し番号、学生番号のように，１つの テーブルの中で同じ値が２回以上出ないと前もって 分かっている属性 • 参照整合性制約 テーブルの主キーを、別のテーブルから参照すると き「値は，参照されるテーブルの主キーの値から選 ぶ」という制約 2

アウトライン 番号 項目 9-1 9-2 復習 主キー 参照と関連 9-3 参照整合性制約 9-4 SQL によるテーブル定義での主キーと参 照整合性制約の指定 9-5 主キーに関する演習（Paiza.IO を使用） 9-6 参照整合性制約に関する演習（Paiza.IO を使用） 3

データベースの構築手順 ID ID データベース データベース 生成 設計 ※ 最初，デー タベースは空 購入 者 商品 ID 名前 数量 ID 購入 者 「こういうテーブルを使い たい」と設定するだけなの で、テーブルは空 数量 1 X 1 10 2 Y 2 5 ID 名前 単価 テーブル定義 商品 ID 単価 1 みかん 50 2 りんご 100 3 りんご 150 テーブル生成 4

テーブル定義 テーブル定義では、 ・テーブル名 ・属性の属性名 ・属性のデータ型 などを設定して、テーブルを定義する 5

SQL コマンドを用いたテーブル定義 ■ SQLite システムの場合 ■ マイクロソフト Access の場合 テーブル名：商品 属性名とデータ型 ID integer 商品 char 単価 integer 6

テーブル間の関連 • テーブルは互いに「関連」しあっている テーブル名：部 テーブル名：社員 １対多 テーブル名：商品 多対多 x と z は，XX に所属 y は，YY に所属 • テーブル社員の複数行が，テーブル部の１行と関 連 • テーブル部の複数行が，テーブル商品の複数行と 関連 7

関連の種類 • 一対一 テーブルの１行が，別のテーブルの１行と関連 • 一対多 テーブルの１行が，別のテーブルの複数行と関連 • 多対多 テーブルの複数行が，別のテーブルの複数行と関連 8

Paiza.IO の使い方 ① ウェブブラウザを起動する ② 次の URL を開く https://paiza.io/ ③ もし，表示が英語になっていたら，日本語に切り 替える

• https://paiza.io/

④ 「コード作成を試してみる」をクリック ⑤ 「MySQL」を選ぶ（左上のボタンをクリックするとメニューが出る）

プログラムの 編集画面 プログラムを 書き換えること ができる 実行ボタン 11

編集画面を確認する。 すでに、SQL が入っているが、使わないので消す。

9-1. 主キー 13

主キー 通し番号、学生番号のように、１つのテーブルの中で 同じ値が２回以上出ないと前もって分かっている属性 ID 1 2 3 名前 単価 みかん 50 りんご 100 りんご 150 主キー 14

主キーがなぜ大切なのか • テーブルのデータを，他のテーブルから参照するとき • 「同じ値が２回以上出ていない」ことを，データベー スシステムで自動チェックしたいとき ID 1 2 3 名前 単価 みかん 50 りんご 100 りんご 150 主キー 15

9-2. 参照と関連 16

参照がどういう場合に役に立つか １対多，多対多の関連があるとき テーブルの主キーを、別のテーブルから参照するよ うにデータベースを作ることが多い 17

１対多の関連のイメージ 部 社員 ID 氏名 住所 所属 ID 部名 所在地 1 x aaa 1 1 XX AAAAA 2 y bbb 2 2 YY BBBBB 3 z ccc 1 18

１対多の関連のイメージ 社員 部 一対多 の関連 ID 氏名 住所 所属 ID 部名 所在地 1 x aaa 1 1 XX AAAAA 2 y bbb 2 2 YY BBBBB 3 z ccc 1 19

主キー，参照 １対多の関連を扱う２つのテーブル ID 氏名 住所 所属 1 x aaa 1 2 y bbb 2 3 z ccc 1 テーブル「社員」の所属の値は、 テーブル「部」のID （主キー） ID 部名 所在地 を参照する 1 XX AAAAA 2 YY BBBBB DDDD 参照 DDDD 主キー 20

多対多の関連のイメージ 商品 部 ID 部名 所在地 1 XX AAAAA 2 YY BBBBB ID 1 2 3 商品名 みかん りんご メロン 単価 50 100 500 21

多対多の関連のイメージ 多対多 部 の関連 ID 部名 所在地 1 XX AAAAA 2 YY BBBBB 部 商品 1 1 1 2 ID 2 1 1 2 3 2 多対多の関連情報 を扱うための追加 3 テーブル 商品 商品名 みかん りんご メロン 単価 50 100 500 22

主キー，参照 部 商品 1 1 1 2 2 1 2 3 ID 部名 所在地 ID 商品名 単価 1 XX AAAAA 1 みかん 50 2 YY BBBBB 2 りんご 100 3 メロン 500 DDDD 参照 DDDD 主キー DDDD 参照 DDDD 主キー 23

9-3. 参照整合性制約 24

参照整合性制約のイメージ メニューから お選びください 枝豆はないんですか？ テーブルのデータを別のテーブルから参照するとき， 制約がつく場合がある 25

主キーと参照と参照整合性制約 参照整合性制約 DDDD ここの値は，メニュー のIDから選ぶ ◇ テーブル名：購入 ID 購入者 商品ID 数量 1 X 1 10 2 Y 2 5 テーブル「購入」の商品IDの値は、 ◇ テーブル名：メニュー 名前 単価 ID 1 みかん 50 2 りんご 100 3 りんご 150 テーブル「商品」のID （主キー） を参照する DDDD 主キー 参照DDDD 26

参照整合性制約がどういう場合に役に立つか １対多，多対多の関連があるとき • テーブルの主キーを、別のテーブルから参照する ようにデータベースを作ることが多い • 参照する側のテーブルには，参照整合性制約があ る 参照整合性制約 値は，参照されるテーブルの主キー の値から選ぶ 27

9-4. SQL によるテーブル定義での 主キーと 参照整合性制約の指定 28

SQL のキーワード • テーブル定義 CREATE TABLE • 主キー PRIMARY KEY • 外部キー FOREIGN KEY, REFERENCES 29

テーブル定義の例（主キー） ◇ テーブル名：メニュー 名前 単価 ID 1 みかん 50 2 りんご 100 3 りんご 150 DDDD 主キー CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 名前 CHAR, 単価 INTEGER); テーブル定義 30

主キーと参照と参照整合性制約 参照整合性制約 DDDD ここの値は，メニュー のIDから選ぶ ◇ テーブル名：購入 ID 購入者 商品ID 数量 1 X 1 10 2 Y 2 5 テーブル「購入」の商品IDの値は、 ◇ テーブル名：メニュー 名前 単価 ID 1 みかん 50 2 りんご 100 3 りんご 150 テーブル「商品」のID （主キー） を参照する DDDD 主キー 参照DDDD 31

テーブル定義の例（参照整合性制約） ◇ テーブル名：購入 ID 購入者 商品ID 数量 1 X 1 10 2 Y 2 5 DDDD 主キー 参照整合性制約 DDDD ◇ テーブル名：メニュー 名前 単価 ID 1 みかん 50 2 りんご 100 3 りんご 150 DDDD 主キー CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 CHAR, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY(商品ID) REFERENCES メニュー(ID)); テーブル定義 32

9-5. 主キーに関する演習 （Paiza.IO を使用） 33

主キーについて集計・集約すると行数は１ ◇ テーブル名：menu ID name price 1 orange 50 2 apple 100 3 apple 150 DDDD 主キー 集計・集約 • 主キーの「ID」でグループを作る • COUNT(*) の結果は，すべて１ 34

テーブル menu （テーブル定義と行の挿入と確認） １から8行目に、次の SQL を入れ、「実行」をクリック。 エラーメッセージが出ないことを確認 CREATE TABLE menu ( ID INTEGER PRIMARY KEY, name TEXT, price TEXT ) engine = InnoDB; INSERT INTO menu VALUES(1, 'orange', 50); INSERT INTO menu VALUES(2, 'apple', 100); INSERT INTO menu VALUES(3, 'apple', 150); 実行をクリックして 何も表示されないのは，正しい動作 「engine = InnoDB」は，MySQL で 参照整合性制約のチェックをONに するためのもの． 35

ここで，補足説明 SQL プログラムは消さずに， 下に書き加えるようにしてください 資料のスクリーンショットでは「行番号」 も付けているので，参考にしてください 36

主キーについて集計・集約すると行数は１ 9 から 11 行目に、次の SQL を入れ、「実行」をクリック。 エラーメッセージが出ないことを確認 SELECT ID, COUNT(*) FROM menu GROUP BY ID; 行数はすべて 1 37

同じ ID の行を挿入することはできない 12 行目に、次の SQL を入れ、「実行」をクリック。 エラーメッセージが出るのは正常動作である INSERT INTO menu VALUES(2, 'melon', 500); ID の値が 2 である行は すでに存在する． ID は主キーである． 同じ ID の行を挿入する ことはできない． 38

9-6. 参照整合性制約に関する 演習（Paiza.IO を使用） 39

主キーと参照と参照整合性制約 参照整合性制約 DDDD ここの値は，メニュー のIDから選ぶ ◇ テーブル名：sales ID who buy num 1 X 1 10 2 Y 2 5 テーブル「sales」の buy の値は、 テーブル「menu」のID （主キー） を参照する ◇ テーブル名：menu ID 1 2 3 name price orange 50 apple 100 apple 150 DDDD 主キー 参照DDDD 40

制約に反するデータを格納しない ◇ テーブル名：sales ID who buy num 1 X 1 10 2 Y 2 5 3 X 10 22 ◇ テーブル名：menu ID 1 2 3 name price orange 50 apple 100 apple 150 sales の buy に「22」を入れることは，参照整合性制約に 違反 41

テーブル menu （テーブル定義と行の挿入と確認） １から8行目を残す．9 行目以降はすべて消す。 「実行」をクリック。エラーメッセージが出ないことを確認 CREATE TABLE menu ( ID INTEGER PRIMARY KEY, name TEXT, price TEXT ) engine = InnoDB; INSERT INTO menu VALUES(1, 'orange', 50); INSERT INTO menu VALUES(2, 'apple', 100); INSERT INTO menu VALUES(3, 'apple', 150); 実行をクリックして 何も表示されないのは，正しい動作 「engine = InnoDB」は，MySQL で 参照整合性制約のチェックをONに するためのもの． 42

ここで，補足説明 SQL プログラムは消さずに， 下に書き加えるようにしてください 資料のスクリーンショットでは「行番号」 も付けているので，参考にしてください 43

テーブル sales （テーブル定義と行の挿入と確認） 9 から 17 行目に、次の SQL を入れ、「実行」をクリック。 エラーメッセージが出ないことを確認 CREATE TABLE sales ( ID INTEGER PRIMARY KEY, who TEXT, buy INTEGER, num INTEGER, FOREIGN KEY(buy) REFERENCES menu(ID) ) engine = InnoDB; INSERT INTO sales VALUES(1, 'X', 1, 10); INSERT INTO sales VALUES(2, 'Y', 2, 5); 実行をクリックして 何も表示されないのは，正しい動作 44

参照整合性制約に違反する行を挿入することはでき ない 18 行目に、次の SQL を入れ、「実行」をクリック。 エラーメッセージが出るのは正常動作である INSERT INTO sales VALUES(3, 'X', 22, 1); sales の buy に「22」を入れること は，参照整合性制約に違反 45

全体まとめ • 主キー 主キーは，通し番号、学生番号のように，１つの テーブルの中で同じ値が２回以上出ないと前もって 分かっている属性 • 参照整合性制約 テーブルの主キーを、別のテーブルから参照すると き「値は，参照されるテーブルの主キーの値から選 ぶ」という制約 46