データ定義言語

このページでは、CREATE TABLEと列制約・表制約、データ型とトリガ、ビューの作り方と更新できないビュー、GRANTとREVOKEによる権限の管理がわかります。

• 一言で言うと：
  • 表やビューを作る（定義する）SQLと、利用者に権限を与える・取り消すSQLの話
  • CREATE TABLEで制約（主キー、UNIQUE、外部キー、CHECK、NOT NULL、DEFAULT）を付け、CREATE VIEWで仮想表を作り、GRANT・REVOKEでアクセス権を管理する

実表を作る（CREATE TABLE、列制約・表制約、データ型、トリガ）→ 仮想表を作る（CREATE VIEW、更新できないビュー）→ 権限を管理する（GRANT、REVOKE）

覚え方の軸

• 制約は「一意・参照・検査・非NULL・既定値」の5つ
  • PRIMARY KEYは、一意性＋NOT NULL（主キー）。UNIQUEは、一意性だけ（NULLは許す）
  • REFERENCESは、参照制約（外部キー）。CHECKは、値の条件。NOT NULLは、空値禁止。DEFAULTは、既定値
• 複数の列にまたがるキーは「表制約」で書く
  • PRIMARY KEY(列名リスト)、UNIQUE(列名リスト)、FOREIGN KEY(列名リスト) REFERENCES 被参照表(列名リスト)
  • 表を作る順は、被参照表（主キー側）が先
• ビューは「実体のない窓」
  • 目的は、データを隠して守ることと、操作を楽にすること
  • 式・集合関数・DISTINCT、同じ列の複数指定、GROUP BY・HAVING、更新不可ビューの利用があると更新できない
• 権限は「GRANTで与えてREVOKEで取る」
  • ON 表名 TO 利用者（GRANT）、ON 表名 FROM 利用者（REVOKE）
  • 列を指定できるのはSELECT・INSERT・UPDATEだけ。DELETEは列指定できない
  • WITH GRANT OPTIONで、もらった権限を他人に与えられる

実表の定義：CREATE TABLEと制約（★）

① CREATE TABLE文

• CREATE TABLE文：
  • 表の定義に使う
• 基本構文：

CREATE TABLE 表名
  (列名1 データ型 [列制約],
   列名2 データ型 [列制約],
     :
   [表制約])

② 列制約

• 列制約：
  • 表を構成する列に対する制約

列制約（★）

制約	指定	中身
一意性制約	PRIMARY KEY	主キーに指定。〔形式〕列名 データ型 PRIMARY KEY
	UNIQUE	値の重複を認めない。〔形式〕列名 データ型 UNIQUE
参照制約	REFERENCES 被参照表名[(列名リスト)]	外部キーに指定。参照される側の表（被参照表）の表名と列名を指定する。列名を省略すると主キーを参照する。〔形式〕列名 データ型 REFERENCES 被参照表(列名)
検査制約	CHECK(探索条件)	登録できる値の条件を指定。〔例〕“評価”列の値を1以上5以下にする：評価 INTEGER CHECK (評価 BETWEEN 1 AND 5)
非NULL制約	NOT NULL	空（NULL）値を認めない列に指定
DEFAULT制約	DEFAULT	行の挿入時、値が指定されていない列に入れる既定値を指定

• PRIMARY KEYとNOT NULL：
  • PRIMARY KEY指定は、必然的にNOT NULL制約を兼ねる
• REFERENCESの列名：
  • 主キーを参照する場合は、列名を省略できる
  • UNIQUE指定された列（主キー以外の候補キー）を参照する場合は、明記する
• 一意性制約：
  • 同じ表の中に同じ値が複数ないことを保証する制約
  • 主キーの列には、一意性制約とNOT NULL制約を兼ねるPRIMARY KEY指定をする
  • 単に値の重複を認めない列（候補キーなど）には、UNIQUE指定をする
• UNIQUEとNULL：
  • 一般に、NULL値は重複値として扱われないので、UNIQUE単独の指定なら、NULL値は許される
• 主キーに必要なもの：
  • 一意性制約のほか、実体を保証するためのNOT NULL制約が必要
• 参照制約：
  • 外部キーの値が、被参照表に存在することを保証する制約
  • 外部キーの列には、REFERENCES指定（参照指定）をする

③ 表制約

• 表制約：
  • 表定義の要素として定義する制約
  • 一意性制約、参照制約、検査制約は、表制約にもできる
• 表制約が必要なとき：
  • 列制約は1つの列に対する制約
  • だから、主キーや外部キーが複数の列でできている場合は、列制約では定義できない。このときは表制約を使う

表制約の書き方（★）

• 一意性制約の定義：
  • 主キー指定：PRIMARY KEY(列名リスト)
  • UNIQUE指定：UNIQUE(列名リスト)
• 参照制約の定義（外部キーと参照指定）：
  • FOREIGN KEY(列名リスト) REFERENCES 被参照表(列名リスト)
  • FOREIGN KEYで外部キーを指定し、REFERENCES句で被参照表とその列を指定する

④ SQLの主なデータ型

SQLデータ型

分類	データ型	中身
文字型	CHARACTER(n)	nバイトの固定長文字列。短縮形はCHAR
	CHARACTER VARYING(n)	最大nバイトの可変長文字列。短縮形はVARCHAR
数値型	INTEGER	整数値。短縮形はINT
	NUMERIC(m [,n])	固定小数点数。mは全体の桁数、nは小数部の桁数
ビット型	BIT(n)	nビットの固定長ビット列
	BIT VARYING(n)	最大nビットの可変長ビット列。短縮形はVARBIT
	BLOB(x)	大量のバイナリデータ（ビット列）。大きさxは、k（キロ）、M（メガ）、G（ギガ）で指定

• CHARとVARCHARの例え：
  • CHARは、マス目が決まった原稿用紙。短い文字でも残りは空白で埋まる
  • VARCHARは、書いた分だけ使うメモ帳（上限だけ決まっている）
• NUMERICの例：
  • NUMERIC(5,2)なら、全体5桁のうち小数部2桁なので、最大 999.99 まで入る
• 注意（新しい情報）：
  • BIT型とBIT VARYING型は、SQL標準（SQL:2003）で削除されていて、今の多くのデータベース製品では使えない（代わりにBINARY型やBOOLEAN型などを使う）
  • 試験では、上の表のとおり覚えておけばよい

⑤ 実表の定義例

部門表の定義

CREATE TABLE 部門
   (部門コード CHAR(3),
    部門名     VARCHAR(20) NOT NULL,
    PRIMARY KEY(部門コード))

社員表の定義

CREATE TABLE 社員
   (社員コード  CHAR(3),
    社員名      VARCHAR(20) NOT NULL,
    電話番号    CHAR(20)    UNIQUE,
    住所        VARCHAR(40) DEFAULT '未定',
    部門コード  CHAR(3),
    PRIMARY KEY(社員コード),
    FOREIGN KEY(部門コード)
          REFERENCES 部門(部門コード) ON DELETE CASCADE)

• 定義例の読み方：
  • 部門コード・社員コードは、表制約のPRIMARY KEYで主キー
  • 社員名・部門名は、NOT NULL（空欄不可）
  • 電話番号は、UNIQUE（重複不可だが、空欄は可）
  • 住所は、値がなければ‘未定’が入る（DEFAULT）
  • 部門コードは、FOREIGN KEYで外部キー。部門表の部門コードを参照する
• 表を定義する順番：
  • 主キー側である被参照表から定義する（部門表 → 社員表）
• DEFAULT句：
  • 列値が登録されない場合の既定値を指定する
• ON DELETE CASCADE：
  • 部門表の行を削除すると、それを参照している社員表の行もすべて削除される
  • 参照動作指定を省略した場合は「NO ACTION」となり、参照している行があれば部門表の行は削除できない

データベースのトリガ

• トリガ：
  • 表に対する更新処理をきっかけに、あらかじめCREATE TRIGGER文で定義しておいた、他の表に対する更新処理を自動的に起動・実行する機能
• 実行されるタイミング：
  • 更新前（BEFORE）と更新後（AFTER）の2つ
• 例：
  • 「CREATE TRIGGER トリガ名 AFTER INSERT ON 表名」の後に、INSERTをきっかけに起動実行する更新処理を書く
• トリガの例え：
  • 「売上表に1行追加されたら、在庫表の数を自動で1つ減らす」という、からくり仕掛け

ビューの定義：実体のない窓（★）

① ビューとは

• 実表：
  • ディスク装置上に存在し、実際にデータが格納される表
• ビュー：
  • 実表の一部、あるいは複数の表から必要な行や列を取り出し、あたかも1つの表であるかのように見せかけた仮想表
  • 仮想表だが、利用者から見れば実表と同じで、データを検索するだけなら、制約はあるものの同じように操作できる

ビューの目的（POINT）（★）

• データを隠して守る：

  • 行や列を特定の条件で絞り込んだビューだけをアクセスさせることで、基となる表（基底表）のデータの一部を隠蔽して保護する手段を提供する

• 操作を楽にする：

  • 利用者が必要とするデータをビューにまとめることで、表操作を容易に行えるようにする

• 体現ビュー（materialized view）：

  • ビューは仮想的な表なので、一般には実体化されず、データ格納領域をとらない
  • これに対し、パフォーマンス向上のために、実表のように実体化されるビューがある。これを体現ビューという
  • 一般には「実体化ビュー」や「マテリアライズドビュー」と呼ばれることが多い

• ビューの性質：

  • ビューは、必要なデータを導出して作った仮想表（導出表に名前を付けたもの）
  • だから、基になる表に新しい列が追加されても、既存のビューには影響がない（再定義の必要はない）
  • ビューに対する参照や更新処理は、基の表に対する参照や更新処理に変換されて実行される

• ビューの例え：

  • 大きな倉庫（実表）の壁に開けた「窓」。窓から見えるのは、見せたい棚だけ。窓そのものには荷物は入っていない

② CREATE VIEW文

• CREATE VIEW文：
  • ビューの定義に使う

CREATE VIEW ビュー名 [(列名1, 列名2, …)]
  AS 問合せ（SELECT）

• ビューの定義の仕方：
  • 対象となる実表（あるいは他のビュー）から、SELECT文で必要なデータを導出する方法で定義する
• ビューの列名：
  • 命名規則はなく、基の表の列名と違う列名でも定義できる
  • ただし、列数は、AS句に続くSELECTの結果の列数と同じである必要がある
  • 列名は省略でき、省略した場合は、SELECTの結果の列名がそのまま使われる

ビューの定義例

• 作りたいビュー：
  • 社員表と部門表を基に、社員コード、社員名、所属部門名からなるビュー「社員2」

CREATE VIEW 社員2 (社員コード, 社員名, 所属部門名)
  AS SELECT A.社員コード, A.社員名, B.部門名
       FROM 社員 A, 部門 B
       WHERE A.部門コード = B.部門コード

• 結果：
  • （101、森田彩、教育）、（102、西川大輔、教育）、（103、山口直美、開発）
  • 部門表の「部門名」を、ビューでは「所属部門名」という違う列名にしている

③ ビューの更新

• ビューの更新の仕組み：
  • ビューへの更新処理は、ビュー定義を基に、ビューが参照している表（基底表）に対する更新処理に変換されて実行される
  • だから、基底表が更新可能で、かつ、更新対象となる列や行が唯一に決まる場合に限り、更新できる

更新不可能なビュー（POINT）（★）

• SELECT句に式・集合関数・DISTINCT：

  • SELECT句に、式や集合関数、DISTINCTを指定している

• 同じ列を複数指定：

  • SELECT句に、基底表の同じ列を複数指定している

• 更新不可ビューを使っている：

  • FROM句に、更新不可ビュー（読み取り専用ビュー）を指定している

• GROUP BY・HAVING：

  • GROUP BY句やHAVING句を使っている

• 同じ列を複数指定した例：

  • 「CREATE VIEW 社員3 (社員名1, 社員名2) AS SELECT 社員名, 社員名 FROM 社員」は、作れるが更新できない

• 更新できない理由の例え：

  • 「部門ごとの平均年齢」（集合関数）のビューで、平均年齢を30歳に書き換えても、どの社員の年齢をいくつにすればよいか決まらない

• ビューの定義に必要な権限：

  • ビューを定義するには、基となる表（基底表）に対するSELECT権限が必要
  • 更新可能なビューを定義するには、SELECT権限のほか、INSERT、UPDATE、DELETE権限が必要

アクセス権限の付与と取消：GRANTとREVOKE（★）

① アクセス権限の付与（GRANT）

• アクセス権限：
  • 複数の利用者がデータベースを使えるようにするには、その利用者に、表やビューへのアクセス権限（SELECT権限、INSERT権限、UPDATE権限、DELETE権限）を付与する必要がある
• GRANT文：
  • 権限の付与に使う

GRANT 権限リスト ON 表名 TO 利用者リスト
GRANT ALL PRIVILEGES ON 表名 TO 利用者リスト

• 権限リスト：
  • 付与する権限を、複数ならカンマで区切って書く
• ALL PRIVILEGES：
  • すべての権限を付与できる
• 列ごとの権限：
  • 表の中の特定の列にだけ権限を付与する場合は、権限の直後に（列名1, 列名2, …）と書く
  • 列を指定できるのは、SELECT権限、INSERT権限、UPDATE権限だけ
  • DELETE権限は、列指定できない（行ごと消すので、列だけ消すことはない）
• 例：
  • 社員表に対するSELECT・INSERT権限をユーザ1に付与：GRANT SELECT, INSERT ON 社員 TO ユーザ1
  • 社員表の社員コードと社員名に対するSELECT権限をユーザ2に付与：GRANT SELECT(社員コード, 社員名) ON 社員 TO ユーザ2
• その他の権限：
  • 上の4つのほかに、表参照権限（REFERENCES）やトリガ生成権限（TRIGGER）などがあるが、試験には出ていない
• WITH GRANT OPTION：
  • 「その利用者に、同じ権限を他の利用者に付与することを許可する」という指定
  • 例えば、利用者Bに、A表のSELECT権限と、その付与権限を与える：GRANT SELECT ON A表 TO 利用者B WITH GRANT OPTION
• GRANTの例え：
  • 社員証に「資料室の閲覧OK」「書き込みOK」と権限を書き込む。WITH GRANT OPTIONは「他の人にも閲覧OKを書き込んでよい」という権限まで渡すこと

② アクセス権限の取消（REVOKE）

• REVOKE文：
  • 一度付与した権限を取り消すときに使う

REVOKE 権限リスト ON 表名 FROM 利用者リスト
REVOKE ALL PRIVILEGES ON 表名 FROM 利用者リスト

• TOとFROMの違い：
  • GRANTは「TO 利用者」（利用者へ与える）
  • REVOKEは「FROM 利用者」（利用者から取り上げる）
• REVOKEの削除動作：
  • SQL標準（JIS X 3005-2:2015）では、REVOKE文の末尾に削除動作（CASCADEまたはRESTRICT）を指定するとしている
  • でも、実際のデータベース製品では省略できるので、この点を突いた問題は出ない

混同しやすいペア

• 列制約 ／ 表制約
  • 列制約は、1つの列に対する制約（列の定義の後ろに書く）
  • 表制約は、表定義の要素として書く制約。複数の列でできたキーはこちら
• PRIMARY KEY ／ UNIQUE
  • PRIMARY KEYは、一意性制約＋NOT NULL制約（主キー）
  • UNIQUEは、一意性制約だけ（NULLは許される。候補キーなど）
• REFERENCES ／ FOREIGN KEY
  • REFERENCESは、参照先（被参照表とその列）を指定する
  • FOREIGN KEYは、表制約で外部キーにする列を指定する
• CHECK ／ NOT NULL
  • CHECKは、登録できる値の条件（検査制約）
  • NOT NULLは、空値を認めない（非NULL制約）
• DEFAULT ／ NOT NULL
  • DEFAULTは、値が指定されないときに入れる既定値
  • NOT NULLは、空値そのものを禁止する
• CHAR ／ VARCHAR
  • CHAR（CHARACTER）は、固定長の文字列
  • VARCHAR（CHARACTER VARYING）は、最大長だけ決まった可変長の文字列
• INTEGER ／ NUMERIC
  • INTEGER（INT）は、整数値
  • NUMERIC(m, n)は、固定小数点数（全体m桁、小数部n桁）
• BIT ／ BIT VARYING ／ BLOB
  • BITは、固定長のビット列
  • BIT VARYING（VARBIT）は、可変長のビット列
  • BLOBは、大量のバイナリデータ
• トリガ ／ 参照動作（CASCADE）
  • トリガは、更新をきっかけに、自分で定義した他の表の更新処理を自動実行する（BEFORE、AFTER）
  • CASCADEは、被参照表の削除・変更に合わせて、参照表の行を連鎖して削除・変更する
• 実表 ／ ビュー
  • 実表は、ディスク上にあり、実際にデータが入っている
  • ビューは、実表から導出した仮想表（データ格納領域をとらない）
• ビュー ／ 体現ビュー（実体化ビュー）
  • ビューは、実体化されない仮想表
  • 体現ビューは、性能向上のために実体化されたビュー
• 基底表 ／ ビュー
  • 基底表は、ビューの基になる表
  • ビューへの参照・更新は、基底表への参照・更新に変換されて実行される
• 更新可能なビュー ／ 更新不可能なビュー
  • 更新可能なビューは、基底表が更新可能で、更新する行や列が唯一に決まる
  • 更新不可能なビューは、式・集合関数・DISTINCT、同じ列の複数指定、GROUP BY・HAVING、更新不可ビューの利用があるもの
• GRANT ／ REVOKE
  • GRANTは、権限を付与する（ON 表名 TO 利用者）
  • REVOKEは、権限を取り消す（ON 表名 FROM 利用者）
• ALL PRIVILEGES ／ WITH GRANT OPTION
  • ALL PRIVILEGESは、すべての権限を付与する
  • WITH GRANT OPTIONは、その権限を他の利用者に付与することも許可する
• SELECT・INSERT・UPDATE権限 ／ DELETE権限
  • SELECT・INSERT・UPDATE権限は、列を指定できる
  • DELETE権限は、列を指定できない

---

← 06 データベースの目次に戻る