10. データベース設計ベストプラク

ティス

主キーと外部キー、設計手法

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

金子邦彦

謝辞：この資料では「いらすとや」のイラストを使用しています

①理論と実践の両立

②データベース設計の理解の深化

③データベース運用能力の向上

アウトライン

1. イントロダクション

2. 主キー

3. 外部キーと参照整合性制約

4. データベース設計

SQLFiddle のサイトにアクセス

Webブラウザを使用

1. ウェブブラウザを開く

2. アドレスバーにSQLFiddleのURLを入力

http://sqlfiddle.com/

URLが分からないときは、Googleなどの検索エンジンを利

用。「SQLFiddle」と検索し、表示された結果から

SQLFiddleのウェブサイトをクリック。

SQLFiddle の画面

•右側のパネル: SQL問い合わせ。

SELECT などを入力。

•左側のパネル: テーブル定義、デー

タの追加など。SQLのCREATE TABLE

やINSERT INTO などを入力。

•実行ボタン

結果ウィンドウ

•実行ボタン

SQLFiddle でのデータベース管理システムの選択

（高度な機能）

データベース管理システムの選択

（この授業では MySQL を使用）

10-1. イントロダクション

リレーショナルデータベースの仕組み

•データをテーブルと呼ばれる表形式で保存

•テーブル間は関連で結ばれる。複雑な構造を持ったデータ

を効率的に管理することを可能に。

関連

商品

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品番号

購入

Xさんは、1のみかんと，

3 のメロンを買った

Yさんは、 2 のりんごを買った

商品テーブルの情報

購入テーブルの情報

リレーショナルデータベースの重要性

1. データの整合性: リレーショナルデータベースは、デー

タの整合性を保持するための機能を有する。これにより、

誤ったデータや矛盾したデータが保存されるのを防ぐこ

とができる。

2. 柔軟な問い合わせ（クエリ）能力: リレーショナルデー

タベースのSQL（Structured Query Language）の使用に

より、複雑な検索やデータの抽出が可能になる。

3. トランザクションの機能: 一連の操作全体を一つの単位

として取り扱うことができる機能。これにより、データ

の一貫性と信頼性が向上する。

4. セキュリティ: アクセス権限の設定などにより、セキュ

リティを確保。

データの安全な保管、効率的なデータ検索・操作、ビジネス

や研究の意思決定をサポート。 10

データベース設計

データベース設計は、データベースの構造を定めるプロセス

•テーブル名、属性、データ型、制約、索引

•テーブル間の関係性

考慮事項

•データの冗長性の減少：正規化

•データの整合性の保証：制約

例：関連するテーブル間の参照に関する制約

例：同じ値が２度現れないという制約

•データの検索や操作の効率化：索引、データベースの構造

の簡素化

正規化

目的

•データベースの構造を最適化

•効率的なデータベース管理を実現

方針

テーブルの数を減らすことよりも、データの冗長性（重複）

を減らすことを行う

正規化の例

正規化前正規化後

名前

昼食

料金

そば

250

カレーライス

400

カレーライス

400

うどん

250

名前

昼食

そば

カレーライス

うどん

昼食

料金

そば

250

カレーライス

400

うどん

250

正規化により、元のテーブルにあった冗長性を排除．

冗長なデータがある

冗長なデータがない

名前

昼食

料金

そば

250

カレーライス

400

カレーライス

400

うどん

250

正規化前の問題

更新

350

カレーライスが

400円から 350円に値下げ

昼食の値段が１つのはずなのに、

350, 400 の違った値段の記録が

あり、不整合がある

「400」をすべて「350」に変更

する必要があるが、変更を間

違ったとする

昼食

値段

カレーライス

400

う

どん

250

名前

昼食

カレーライス

う

どん

カレーライス

350

正規化による問題解消

更新カレーライスが

400円から 350円に値下げ

「400」をすべて「350」に変更

するのは1か所で済む。

間違いが起きにくい。

正規化のメリット

•データの冗長性の減少：データの重複を減らす

•異状の防止：更新、削除、挿入時の異状を防ぐ

•効率の向上：データベース全体のサイズが縮小すると、ス

トレージの効率化とデータベース操作の高速化が期待でき

る

•信頼性の確保：異状の防止により、データの信頼性の向上。

•管理の容易化：データベースの管理が容易になる

10-2. 主キー

主キー

•主キーは、テーブルの各行を識別するためのキー

主キー

ID 商品名単価

みかん

りんご

100

メロン

500

主キーの性質

•主キーは必ず違う値になる．（同じ主キーの値が

重複することはない）

主キー

ID 商品名単価

みかん

りんご

100

メロン

500

みかん

りんご

主キーの役割

•商品テーブルで、すべての商品は一意の ID を持つ

•同じ ID のもつ商品は2つ以上ない。

•商品の行を正確に特定するときに便利

例： ID = 2 である行 →１つに定まる

主キー

ID 商品名単価

みかん

りんご

100

メロン

500

みかん

りんご

主キーの選択基準

•一意である：主キーは必ず違う値になる．（同

じ主キーの値が重複することはない）

•不変である：値が変更されることはない

主キー

ID 商品名単価

みかん

りんご

100

メロン

500

みかん

りんご

主キー制約の役割

主キー制約は、テーブルの主キーが「主キーとして

の役割を果たす」ことを保証する仕組み

•一意性の保証：同じ値が重複する場合は制約違

反である

PRIMARY KEY

PRIMARY KEY はテーブル定義時に使用し、「主

キー制約」を示す

ID 商品名単価

みかん

りんご

100

メロン

500

ｒ

CREATE TABLE テーブル名 (

列名1 データ型 PRIMARY KEY,

列名2 データ型,

...

);

CREATE TABLE 商品 (

ID INTEGER PRIMAY KEY,

商品名 TEXT,

単価 INTEGER);

SQL の書き方

主キー

学生ID

科目ID

得点

1001

1002

100

1001

1002

1003

PRIMARY KEY

但し、主キーは1つの属性でも良いし、複数の属性の組み合

わせでもよい（書き方は下のようになる）

ｒ

CREATE TABLE テーブル名 (

列名1 データ型,

列名2 データ型,

...

PRIMARY KEY (列名1, 列名2)

);

SQL の書き方

CREATE TABLE 成績 (

学生ID INTEGER,

科目ID INTEGHER,

得点 INTEGER,

PRIMARY KEY(学生ID, 科目ID));

主キー

学生ID 科目ID 得点

1001

1002

100

1001

1002

1003

PRIMARY KEY の効果

「PRIMARY KEY」の使用により、

同じ学生が同じ科目に複数回登録

することはできなくなる

ID 商品名単価

みかん

りんご

100

メロン

500

ｒ

主キー

ID 商品名単価

みかん

りんご

100

メロン

500

いちご

1000

「PRIMARY KEY」の使用により、

同じID の商品を複数登録すること

はできなくなる。

ｒ

主キー

学生ID 科目ID 得点

1001

1002

100

1001

1002

1003

1001

SQL によるテーブル定義

•テーブル名：商品

•属性名：ID、商品名、単価

•属性のデータ型：数値、テキスト、数値

•データの整合性を保つための制約：主キー制約

CREATE TABLE 商品 (

ID INTEGER PRIMAY KEY,

商品名 TEXT,

単価 INTEGER);

データ追加のSQL

商品

INSERT INTO 商品 VALUES(1, 'みかん', 50);

INSERT INTO 商品 VALUES(2, 'りんご', 100);

INSERT INTO 商品 VALUES(3, 'メロン', 500);

商品名

単価

みかん

りんご

100

メロン

500

演習１．テーブル定義と

データの追加、主キー制約

【トピックス】

1. SQL によるテーブル定義

2. 主キー制約 PRIMARY KEY

3. SQL によるデータの追加

4. 問い合わせ（クエリ）による

確認

Webブラウザを使用

①アドレスバーにSQLFiddleのURLを入力

http://sqlfiddle.com/

URLが分からないときは、Googleなどの検索エンジンを利

用。「SQLFiddle」と検索し、表示された結果から

SQLFiddleのウェブサイトをクリック。

②左側のパネルに、テーブル定義とデータの追加を行う

SQL を入れる。

CREATE TABLE 商品 (

ID INTEGER PRIMARY KEY,

商品名 TEXT,

単価 INTEGER);

INSERT INTO 商品 VALUES(1, 'みかん', 50);

INSERT INTO 商品 VALUES(2, 'りんご', 100);

INSERT INTO 商品 VALUES(3, 'メロン', 500);

③「Build Schema」をクリック

④右側のパネルに、問い合わせ（クエリ）を行うSQL を入

れる。

⑤「Run SQL」をクリック

SQL 文が実行され、結果が表示される。

⑥下側のウインドウで、結果を確認。

select * FROM 商品;

⑦左側のパネルに、テーブル定義とデータの追加を行う

SQL を入れる。前のものと比べて１行増えている。

CREATE TABLE 商品 (

ID INTEGER PRIMARY KEY,

商品名 TEXT,

単価 INTEGER);

INSERT INTO 商品 VALUES(1, 'みかん', 50);

INSERT INTO 商品 VALUES(2, 'りんご', 100);

INSERT INTO 商品 VALUES(3, 'メロン', 500);

INSERT INTO 商品 VALUES(1, 'いちご', 1000);

⑧「Build Schema」をクリック

⑨「主キー制約」に違反している旨のエラーメッセージが

出て実行できないことを確認する

これは、制約違反のチェック機能が働いたため、正常動作

自習１．複数属性の主キー制約

主キー制約の SQL での書き方について理解を深める

次の SQL を実行しなさい

左側のパネル

CREATE TABLE 成績 (

学生ID INTEGER,

科目ID INTEGER,

得点 INTEGER,

PRIMARY KEY(学生ID, 科目ID));

INSERT INTO 成績 VALUES(1, 1001, 90);

INSERT INTO 成績 VALUES(1, 1002, 100);

INSERT INTO 成績 VALUES(2, 1001, 85);

INSERT INTO 成績 VALUES(2, 1002, 90);

INSERT INTO 成績 VALUES(2, 1003, 95);

右側のパネル

select * FROM 成績;36

自習２．主キー制約の違反

主キー制約に違反する場合、実行できないことを確認する

次の SQL を実行しなさい（自習１に１行増やしている）

左側のパネル

CREATE TABLE 成績 (

学生ID INTEGER,

科目ID INTEGER,

得点 INTEGER,

PRIMARY KEY(学生ID, 科目ID));

INSERT INTO 成績 VALUES(1, 1001, 90);

INSERT INTO 成績 VALUES(1, 1002, 100);

INSERT INTO 成績 VALUES(2, 1001, 85);

INSERT INTO 成績 VALUES(2, 1002, 90);

INSERT INTO 成績 VALUES(2, 1003, 95);

INSERT INTO 成績 VALUES(1, 1001, 95);

•自習１

•自習２

主キー制約に違反するため実行できない

Sqlfiddle が混みあっていて

実行できないときは、

少し待ってから実行してみる

主キーのまとめ

主キーの目的

•テーブル内の各行を一意に識別。

主キーの性質

•一意性：重複しない。

•不変性：値が変更されない。

主キー制約

•一意性を保証。

•テーブル定義時に設定。

主キーの構成

•単一または複数の属性で構成可能。

10-3. 外部キーと

参照整合性制約

外部キー

外部キーは、他のテーブルの主キーを参照するキー

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

購入テーブルの外部キー「商品

ID」は、購入テーブルの主キー

「ID」を参照

主キー

外部キー

購入テーブル

商品テーブル

参照整合性制約のイメージ

テーブルのデータを別のテーブルから参照するときの

制約として、参照整合性制約がある

商品から

お選びください

枝豆はないんですか？

参照整合性制約の定義と目的

•参照整合性制約は、リレーショナルデータベースにおいて、

異なるテーブル間の関連付けられたデータが一貫性を保つ

ようにするための制約

•外部キーの値が、別のテーブルの主キーと一致することを

保証することができる

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

主キー

外部キー

参照整合性制約の例

商品テーブルに ID が存在しない場合、購入テーブ

ルに、そのIDを持つ行を挿入することはできない

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

主キー

外部キー

DDDD

参照整合性制約

外部キーに入れる値は，

主キーから選ぶ

SQL によるテーブル定義

•テーブル名：購入

•属性名：ID、購入者、商品ID、数量

•属性のデータ型：数値、テキスト、数値、数値

•データの整合性を保つための制約：主キー制約、参照整合

性制約

CREATE TABLE 購入 (

ID INTEGER PRIMARY KEY,

購入者 TEXT,

商品ID INTEGER,

数量 INTEGER,

FOREIGN KEY (商品ID) REFERENCES 商品(ID));

FOREIGN KEY … REFERENCES

PRIMARY KEY … REFERENCES はテーブル定義時に使用

し、あるテーブルの外部キーが別のテーブルの主キーを参照

する「参照整合性制約」を示す

SQL の書き方

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

主キー

外部キー

CREATE TABLE 購入 (

ID INTEGER PRIMARY KEY,

購入者 TEXT,

商品ID INTEGER,

数量 INTEGER,

FOREIGN KEY (商品ID) REFERENCES 商品(ID));

主キー

結合の例

SQLの実行により

新しく生成される

テーブル

ID 商品名単価

購入者

商品番号

みかん

メロン

500

りんご

100

SELECT * FROM 商品

JOIN 購入

ON 商品.ID = 購入.商品番号;結合条件

結合のためのSQL

関連

商品

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品番号

購入

演習３．外部キー，参照整

合性制約

【トピックス】

1. 主キー

2. 外部キー

3. 参照整合性制約

4. PRIMARY KEY

5. FOREIGN KEY …

REFERENCES

Webブラウザを使用

①アドレスバーにSQLFiddleのURLを入力

http://sqlfiddle.com/

URLが分からないときは、Googleなどの検索エンジンを利

用。「SQLFiddle」と検索し、表示された結果から

SQLFiddleのウェブサイトをクリック。

②左側のパネルに、テーブル定義とデータの追加を行う

SQL を入れる。（前のものは不要である）

CREATE TABLE 商品 (

ID INTEGER PRIMARY KEY,

商品名 TEXT,

単価 INTEGER);

INSERT INTO 商品 VALUES(1, 'みかん', 50);

INSERT INTO 商品 VALUES(2, 'りんご', 100);

INSERT INTO 商品 VALUES(3, 'メロン', 500);

CREATE TABLE 購入 (

ID INTEGER PRIMARY KEY,

購入者 TEXT,

商品ID INTEGER,

数量 INTEGER,

FOREIGN KEY (商品ID) REFERENCES 商品(ID));

INSERT INTO 購入 VALUES(1, 'X', 1, 10);

INSERT INTO 購入 VALUES(2, 'Y', 2, 5);

③「Build Schema」をクリック

④右側のパネルに、問い合わせ（クエリ）を行うSQL を入

れる。

⑤「Run SQL」をクリック

SQL 文が実行され、結果が表示される。

⑥下側のウインドウで、結果を確認。

SELECT * FROM 商品;

⑦右側のパネルに、問い合わせ（クエリ）を行うSQL を入

れる。

⑧「Run SQL」をクリック

SQL 文が実行され、結果が表示される。

⑨下側のウインドウで、結果を確認。

SELECT * FROM 購入;

⑩右側のパネルに、問い合わせ（クエリ）を行うSQL を入

れる。

⑪「Run SQL」をクリック

SQL 文が実行され、結果が表示される。

⑫下側のウインドウで、結果を確認。

SELECT 購入.購入者, 商品.商品名, 商品.単価 *購入.数量

FROM 購入

JOIN 商品 ON 購入.商品ID = 商品.ID;

自習３．主キー制約の違反

主キー制約に違反する場合、実行できないことを確認する

次の SQL を実行しなさい（１行増やしている）

左側のパネル

CREATE TABLE 商品 (

ID INTEGER PRIMARY KEY,

商品名 TEXT,

単価 INTEGER);

INSERT INTO 商品 VALUES(1, 'みかん', 50);

INSERT INTO 商品 VALUES(2, 'りんご', 100);

INSERT INTO 商品 VALUES(3, 'メロン', 500);

CREATE TABLE 購入 (

ID INTEGER PRIMARY KEY,

購入者 TEXT,

商品ID INTEGER,

数量 INTEGER,

FOREIGN KEY (商品ID) REFERENCES 商品(ID));

INSERT INTO 購入 VALUES(1, 'X', 1, 10);

INSERT INTO 購入 VALUES(2, 'Y', 2, 5);

INSERT INTO 購入 VALUES(3, 'X', 22, 1);

自習３のヒント：

「22」は、参照整合性制約に違反

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

主キー

外部キー

自習３

参照整合性制約に違反するため実行できない

外部キー、参照整合性制約のまとめ

外部キーの定義

•他のテーブルの主キーを参照するキー

参照整合性制約の目的

•リレーショナルデータベース内で異なるテーブル間のデータが一貫性を保つよ

うにする

•外部キーが参照するテーブルの主キーと一致することを保証

参照整合性制約の例

•商品テーブルに存在しないIDは、購入テーブルに挿入できない。

FOREIGN KEY … REFERENCESの使用

•テーブル定義時に、外部キーが別のテーブルの主キーを参照することを示す。

CREATE TABLE 購入 (

ID INTEGER PRIMARY KEY,

購入者 TEXT,

商品ID INTEGER,

数量 INTEGER,

FOREIGN KEY (商品ID) REFERENCES 商品(ID));

10-4. データベース設計

データベースの構築手順

テーブル定義

「こういうテーブルを使い

たい」と設定するだけなの

で、テーブルは空

データベース

生成

※ 最初，デー

タベースは空

テーブル生成

データベース

設計

ID 購入

者

商品

数量 ID 購入

者

商品

数量

ID 名前単価

みかん

りんご

100

りんご

150

ID 名前単価

主キー制約の重要性

•データの一意性の保証

例: 従業員テーブルで、各従業員に割り当てられた一意の従

業員IDにより、同一人物の重複登録を防止

•データの識別とアクセスの容易化

例: 従業員IDを用いて特定の従業員を識別。外部キーが機能

するためにも利用

参照整合性制約の重要性

•データの整合性の維持

例：従業員テーブルの各従業員の部署が、部署テーブルに実

在することを保証

•データの信頼性の向上

例：存在しない従業員が、給与データに挿入することを防止

•データの更新時の安全性

部署のデータを削除する際、その部署に所属する従業員を確

認

•関連データの整理

FOREIGN KEY とREFERENCES を使用して、異なるテー

ブル間の関連を明確に示すことができる。

データベースの構造が理解しやすくなり、データベースの管

理や変更が容易になる。 62

１対多の関連

商品名

単価

みかん

りんご

100

メロン

500

購入者

商品ID

数量

主キー

外部キー

１人の購入者が、複数の購入を行う

多対多の関連の例

学生ID

科目ID

得点

1001

1002

100

1001

1002

1003

１人の学生が、複数の科目を受講する。１つの科目には

複数の学生が参加する

科目名

1001

国語

1002

算数

1003

理科

ID 学生名

徳川家康

豊臣秀吉

主キー

外部キー外部キー

主キー

データベース設計のプロセス

１．テーブル名の決定

例: 「従業員」、「顧客」、「商品」など、データの種類や目的に

応じた名前。

２．属性の設定

例: 従業員テーブルの属性は「従業員ID」、「名前」、「住所」

３．データ型の選択

例: 「従業員ID」は整数型（INTEGER）、「名前」は文字列型

(TEXT)。

４．制約の設定

例: 主キー制約など

５．索引（インデックス）の作成

例: 頻繁に検索される「従業員ID」や「名前」に索引を設定。

６．テーブル間の関係性

例: 「従業員」テーブルの「部署ID」が「部署」テーブルの「部署

ID」を参照（外部キーとして）。

データベース設計の実践例

•シナリオ

ある大学の学生、講義、および成績管理システム。

•目的

学生の個人情報、登録講義、取得成績を効率的に管理。

「成績管理」のデータベース設計の実践例

シナリオ

ある大学の学生、講義、および成績管理システム。

目的

学生の個人情報、登録講義、取得成績を効率的に管理。

•学生テーブル:

•属性: 学生ID (主キー), 名前, 専攻

•主キー: 学生ID

•講義テーブル:

•属性: 講義ID (主キー), 講義名, 担当教員

•主キー: 講義ID

•成績テーブル:

•属性: 学生ID (外部キー), 講義ID (外部キー), 成績

•外部キー: 学生ID (学生テーブル参照), 講義ID (講義テーブル参照)

•主キー: 学生IDと講義ID

データベースシステムとアプリケーションの連携

メリット

•データの一元管理

データが一箇所に集中され、情報の整合性と一貫性が保

たれる

•効率的なデータアクセス

アプリケーションは、データベースシステムにアクセス

し、迅速なデータ検索と更新が可能

•拡張性と柔軟性

データベースの構造を変更しても、アプリケーション側

の調整が最小限で済む

•データのセキュリティ強化

データベースレベルでのセキュリティ対策により、デー

タの保護が強化される 68

データベース設計のまとめ

主キー制約の重要性

•データの一意性保証と識別の容易化。

参照整合性制約の重要性

•データの整合性維持、信頼性向上、更新時の安全性確保、