データベースと応用システム：SQL(1)表の作成と整合性制約

Transcript

1. (c)長岡技術科学大学 電気系 1 データベースと応用システム ＳＱＬ（１） ～表の作成と整合性制約～ 山本和英 長岡技術科学大学 電気系
2. (c)長岡技術科学大学 電気系 2 ＳＱＬの概要 • ＩＢＭが開発した関係データモデルに基づくデータ ベース言語 • JIS規格(JIS X 3005) • ほとんどのRDBMSで採用されている • アクセスだけでなくテーブルなどの管理機能も標 準化されている • 条件を満たすデータに対して一括処理が可能
3. (c)長岡技術科学大学 電気系 3 ＳＱＬの２言語 • データ定義言語(Data Definition Language, DDL) – テーブルやビューの作成、権限の定義 – CREATE, DROP, GRANT, REVOKE など • データ操作言語(Data Manipulation Language, DML) – データの参照・更新・追加・削除など – SELECT, UPDATE, INSERT, DELETE など ※青字部分をデータ制御言語(Data Control Language, DCL)として３言語とする分類もある。
4. (c)長岡技術科学大学 電気系 4 表定義 CREATE TABLE 商品 ( 　商品コード　CHAR(4) PRIMARY KEY, 　商品名　　　NCHAR(10) NOT NULL, 　単価　　　　INT, 　仕入先コード CHAR(4), 　FOREIGN KEY (仕入先コード) REFERENCES (仕入先), 　CHECK (単価 <= 500) ) 商品コード 商品名 単価 仕入先コード 1320 マウス 300 P200 【商品】
5. (c)長岡技術科学大学 電気系 5 表定義 CREATE TABLE 商品 ( 　商品コード　CHAR(4) PRIMARY KEY, 　商品名　　　NCHAR(10) NOT NULL, 　単価　　　　INT, 　仕入先コード CHAR(4), 　FOREIGN KEY (仕入先コード) REFERENCES (仕入先), 　CHECK (単価 <= 500) ) 商品コード 商品名 単価 仕入先コード 1320 マウス 300 P200 【商品】 主キーである ４文字の文字列 必ず値がある 整数である 【仕入先】の仕入れ先コードが 外部キーである 単価に制約をかける
6. (c)長岡技術科学大学 電気系 6 表の削除 DROP TABLE テーブル名 例： DROP TABLE 商品
7. (c)長岡技術科学大学 電気系 7 整合性制約 • データがデータベース管理者の想定している通りに 入力されることを保証するための仕組み • 表定義時に（CREATE文中に）一緒に定義する。 • テーブルに対しての制約（テーブル制約）とテーブ ル中の列に対しての制約（列制約）がある。
8. (c)長岡技術科学大学 電気系 8 形式制約 入力するデータの型(数字／文字）や文字数（桁）を 指定する。 • 数値型 – INT：大整数、SMALLINT：小整数、NUMERIC(n,m)：全 桁数nで小数点以下m桁、DECIMAL：NUMERICと同じ • 文字列型 – CHAR(n)：固定長文字列n桁(nは省略可能) – VARCHAR(n)：可変長文字列n桁(nは省略可能) • 日付型 – DATE：年月日、TIMESTAMP：年月日と時刻
9. (c)長岡技術科学大学 電気系 9 デフォルト値 • データを追加する時に値を指定しないと、この値が 設定される。 　... 　電話番号 CHAR(15) DEFAULT '0258-46-6000', 　... ※デフォルト値は整合性制約ではないが、記述方法が同じ ためここで紹介しています。
10. (c)長岡技術科学大学 電気系 10 非ナル（NOT NULL）制約 • ある列にNULL値が入らないようにする。 　... 　電話番号 CHAR(15) NOT NULL, 　...
11. (c)長岡技術科学大学 電気系 11 一意性（UNIQUE）制約 • 指定列が一意である （＝同一なものが存在 しない）ことを強制す る。 • 列に対して： 　... 　電話番号 UNIQUE, 　... • テーブルに対して： CREATE TABLE 商品 　(商品名 NCHAR(20), 　… 　UNIQUE(商品名), 　...
12. (c)長岡技術科学大学 電気系 12 主キー制約 • 指定した列、または列の 組み合わせに一つだけ主 キーとしての指定をする。 • 主キー制約 ＝ 一意性制 約 ＋ 非ナル制約 • 列制約： CREATE TABLE 受注 　(受注ID CHAR(5) PRIMARY KEY, 　… • テーブル制約 CREATE TABLE 受注明細 　(受注ID CHAR(5), 　 連番 CHAR(2), 　　... PRIMARY KEY(受注ID, 連番), 　…
13. (c)長岡技術科学大学 電気系 13 検査（CHECK）制約 • 指定した列の内容に条件 を加える。 • 列制約： ... 単価 INT CHECK(単価>=100), ... • テーブル制約 CREATE TABLE 商品 　(単価 INT, 　　... CHECK(単価>=100) 　　...
14. (c)長岡技術科学大学 電気系 14 参照制約（外部キー制約） • 複数の表に参照関係がある場合の制約。以下、Ａ 表のある列がＢ表のある列を参照しているとする。 • Ａ表にデータを追加する際はＢ表にある値かNULLし か追加できない • Ｂ表のデータを削除や更新する際は、Ａ表にその値 が存在しない時にのみ可能となる
15. (c)長岡技術科学大学 電気系 15 参照制約の例 • 列制約： CREATE TABLE 受注 　(得意先ID CHAR(5) REFERENCES 得意先(得 意先ID), 　... • テーブル制約 CREATE TABLE 受注 　(得意先ID CHAR(5), 　　... FOREIGN KEY (得意先 ID) REFERENCES 得意先(得 意先ID), 　...
16. (c)長岡技術科学大学 電気系 16 参照制約：オプション • NO ACTION – 参照元にあると参照先を削除・更新できない – デフォルト • CASCADE – 参照元にあると参照先も連携して削除する • SET NULL – 参照元にあると参照先も連携して削除し、参照元の列 にNULLを設定する
17. (c)長岡技術科学大学 電気系 17 参照制約オプションの例 CREATE TABLE 受注 　(得意先ID CHAR(5), 　　... FOREIGN KEY (得意先ID) 　　REFERENCES 得意先(得意先ID), 　　ON DELETE SET NULL 　...
18. (c)長岡技術科学大学 電気系 18 その他の制約 • 存在制約 – あるデータが存在するためには、別のデータが必要で ある • 更新制約 – 値を更新する場合、適当な値を入力しなければならな い（例：月の欄は１～１２） • 表明 – 複数の表にまたがる複雑な制約を定義できる