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

376 Views

January 12, 22

スライド概要

リレーショナルデータベースの基本(スライド資料とプログラム例)
https://www.kkaneko.jp/de/ds/index.html


ds-1. データベースとは,データベースシステムとは,情報とデータ
ds-2. SQL,SQL のデータ型,テーブル定義,問い合わせ(クエリ)
ds-3. ER 図,関連,異状, テーブル分解
ds-4. 問い合わせ(クエリ)
ds-5. 集計・集約
ds-6. 並べ替え(ソート)
ds-7. 結合
ds-8. データベースの異状,分解と結合
ds-9. 主キー,参照整合性制約,従属
ds-10. 中間まとめ,データベースの応用,データベースの種類
ds-11. SQL のIN,副問い合わせ
ds-12. データベースの AND,OR,NOT
ds-13. データベース操作,トランザクション,リカバリ,ロック,同時実行制御
ds-14. 従属,正規形,正規化
ds-15. さまざまなデータベース

YouTube 再生リスト「リレーショナルデータベースの基本」
https://youtube.com/playlist?list=PLwoDcGBEg9WGKPP6dExr8DcUf9nV2kYGD

金子邦彦研究室ホームページ:
https://www.kkaneko.jp/index.html

profile-image

金子邦彦(かねこくにひこ) 福山大学・工学部・教授 ホームページ: https://www.kkaneko.jp/index.html 金子邦彦 YouTube チャンネル: https://youtube.com/user/kunihikokaneko

シェア

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

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

2.

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

3.

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

4.

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

5.

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

6.

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

7.

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

8.

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

9.

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

10.

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

11.

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

12.

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

13.

9-1. 主キー 13

14.

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

15.

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

16.

9-2. 参照と関連 16

17.

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

18.

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

19.

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

20.

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

21.

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

22.

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

23.

主キー,参照 部 商品 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

24.

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

25.

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

26.

主キーと参照と参照整合性制約 参照整合性制約 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.

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

28.

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

29.

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

30.

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

31.

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

32.

テーブル定義の例(参照整合性制約) ◇ テーブル名:購入 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

33.

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

34.

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

35.

テーブル menu (テーブル定義と行の挿入と確認) 1から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

36.

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

37.

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

38.

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

39.

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

40.

主キーと参照と参照整合性制約 参照整合性制約 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

41.

制約に反するデータを格納しない ◇ テーブル名: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

42.

テーブル menu (テーブル定義と行の挿入と確認) 1から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

43.

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

44.

テーブル 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

45.

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

46.

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