SQLで表のデータを制限する：ORACLE MASTER Bronze SQL基礎I 講座（11）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！
前回「SQLでデータを自在に操作する」で、データ操作を紹介しました。今回から2回にわたり、オブジェクトの作成と管理について学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
- 第1回 Oracleで使うSQLの基礎を学ぶ
- 第2回 SQL問い合わせによる行の制限とソート
- 第3回 SQLで使える関数の基礎知識
- 第4回 SQLの関数を使いこなす
- 第5回 SQLの関数でデータを集計する
- 第6回 SQLで複数の表からデータを取り出す
- 第7回 SQLの外部結合でデータを取り出す
- 第8回 SELECT文中のSELECT文、副問い合わせ
- 第9回 副問い合わせの構文を覚える
- 第10回 SQLでデータを自在に操作する
- 第11回 SQLで表のデータを制限する
- 最終回 SQLで便利なオブジェクトを使いこなす
理解しておきたいこと
1．確認しておきたい内容
- 表の作成
- 制約の作成および保守
2．基本のデータベースオブジェクト
データベースで扱うオブジェクトはさまざまですが、基本のデータベースオブジェクトには次のものがあります。
| 表 | データを格納する基本となるオブジェクト。定義として列を持ち、行を格納する |
|---|---|
| ビュー | 1つ以上の表データに対する問い合わせ結果を表示するための論理的な定義。SELECT文に名前を付けたもの |
| シノニム | オブジェクトに対する別名 |
| 索引 | 問い合わせのパフォーマンスを向上させるため、列値と行へのポインタを格納したもの |
| 順序 | 一意的な番号を生成するもの |
どのようなときにどのオブジェクトを使用するかを把握しておきましょう。
3．表の作成
表を作成するには、事前に表を作成する権限（CREATE TABLE権限）と領域割り当て（QUOTA）が必要です。
- データ型：列のデータ型と最大サイズ
- DEFAULT：列のデフォルト値。列に値が格納されないときに使用される
副問い合わせを使用し、別の表から定義をコピーして表を作成することもできます。
表名や列名は分かりやすいものを使用しましょう。次のような命名規則があります。
- 文字から始める（数字から始めることはできない）
- 長さは1?30bytes
- 使用する文字は、英数字（A?Z、a?z、0?9）、$、_、#
- Oracleデータベースの予約語は使用しない（TABLEやUSERなど）
- 同じユーザーが所有しているほかのオブジェクトと重複させない（異なる表の異なる列であれば同じ名前は可能）
表を作成するには、列を定義する必要があります。列には、格納するデータのタイプを識別するためのデータ型が必要です。Oracleデータベースにはさまざまなデータ型がありますが、基本的なものは以下のとおりです。
| データ型 | 説明 | データ例 |
|---|---|---|
| CHAR(サイズ) | 固定長の文字データ。サイズ不足分は空白で埋める。サイズは1?2000bytes | 'ABC ' |
| VARCHAR2(サイズ) | 可変長の文字データ。サイズは1?4000bytes | 'ABC' |
| LONG | 可変長の文字データ。サイズは最大2Gbytes | 'ABC' |
| CLOB | 可変長の文字データ。サイズは最大4Gbytes | 'ABC' |
| NUMBER(p,s) | 数値データ。精度pと位取りsを指定しない場合は浮動小数点（38けた） | 123、123.45 |
| DATE | 日付データ。4けたの年、月、日、時間、分、秒を紀元前4712年1月1日から西暦9999年12月31日まで扱える | '2006-03-15 17:15:30' |
| TIMESTAMP | 秒以下の小数部（最大9けた、デフォルト6けた）を含むDATE型の拡張データ型 | 06-03-15 00:18:22.627000 |
| INTERVAL DAY TO SECOND | 日、時間、分、秒による間隔データ | INTERVAL '10 12:45:30.001' DAY TO SECOND（10日12時間45分30.001秒） |
| INTERVAL YEAR TO MONTH | 年、月による間隔データ | INTERVAL '10-6' YEAR TO MONTH（10年6カ月） |
| RAW | バイナリデータ。サイズは1?2000bytes | 画像ファイルや音声ファイルなど |
| LONG RAW | バイナリデータ。サイズは最大2Gbytes | 画像ファイルや音声ファイルなど |
| BLOB | バイナリデータ。サイズは最大4Gbytes | 画像ファイルや音声ファイルなど |
| BFILE | 外部ファイルとしてのバイナリデータ。サイズは最大4Gbytes | 画像ファイルや音声ファイルなど |
| ROWID | ROWID（データベース内の行を一意に識別できるアドレス）を格納 | AAAL+ZAAEAAAAAdAAA |
INSERT文では、VALUES句の代わりに副問い合わせを使用することができます。副問い合わせの結果は、INSERTの列の数とデータ型に一致する必要があります。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
問題1
日付／間隔データ型を3つ選択しなさい。
a．TIMESTAMP
b．INTERVAL MONTH TO DAY
c．INTERVAL DAY TO SECOND
d．INTERVAL YEAR TO MONTH
e．TIME
正解：a、c、d
解説
Oracleデータベースで日付を扱うデータ型としては、DATEデータ型とTIMESTAMPデータ型があります（正解a）。DATEデータ型は年、月、日、時間、分、秒を扱うことができ、TIMESTAMPデータ型はDATEデータ型に加え、小数点以下の秒数を最大9けた扱うことができます。
間隔データとしては、INTERVAL YEAR TO MONTHデータ型とINTERVAL DAY TO SECONDデータ型があります（正解c、正解d）。INTERVAL YEAR TO MONTHデータ型は年と月による間隔データ、INTERVAL DAY TO SECONDデータ型は日、時間、分、秒（小数点以下も含める）による間隔データを扱います。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
1行目はSYSDATE関数の結果です。SYSDATE関数はDATEデータ型のため、秒の小数部分は格納されません。2行目のSYSTIMESTAMP関数はTIMESTAMPデータ型のため、秒の小数部分も格納されます。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
間隔データを使用すると、より高度な加算／減算などができます。上記の例では10年6カ月を加算しています。
選択肢bのような間隔データはありません。正しく覚えておきましょう。
問題2
次の定義を確認してください。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
注文日に値がセットされないときは今日の日付が格納されるようにORDERS表を定義している文を選択しなさい。
a．
CREATE TABLE orders(
ord_id NUMBER(3),
cust_name VARCHAR2(10),
ord_status NUMBER(2),
ord_date DATE DEFAULT SYSDATE);
b．
CREATE TABLE orders(
ord_id NUMBER(3),
cust_name VARCHAR2(10),
ord_status NUMBER(2),
ord_date DATE DEFAULT = SYSDATE);
c．
CREATE TABLE orders(
ord_id NUMBER(3),
cust_name VARCHAR2(10),
ord_status VARCHAR2(5),
ord_date DATE DEFAULT SYSDATE);
d．
CREATE TABLE orders(
ord_id NUMBER(3),
cust_name VARCHAR2(10),
ord_status VARCHAR2(5),
ord_date DATE DEFAULT = SYSDATE);
正解：c
解説
列のデフォルト値を設定する場合、「列名 データ型 DEFAULT デフォルト値」の構文を使用します。デフォルト値には関数を使用することもできます。選択肢bと選択肢dのように「=」は使用しません。構文を確認しておきましょう。
選択肢aは一見すると正しいのですが、問題要件の「ORD_STATUS列は文字データ」を満たしていません。NUMBERデータ型は数値データを扱います。このような引っ掛けも多いので、よく問題を読むようにしましょう。
問題3
次の文を確認してください。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
この文の結果として正しいものを選択しなさい。
a．デフォルト値は列の変更では定義できないため、エラーとなる
b．数値データを変更することはできないため、エラーとなる
c．デフォルト値は、次回以降の更新で使用される
d．既存レコードの該当する列値がデフォルト値で更新される
正解：c
解説
表の定義を変更するには、ALTER TABLE文を使用します。ALTER TABLE MODIFY文では列の定義を変更できます。列のデータ型や最大サイズ、デフォルト値の定義やNOT NULL制約の設定／解除に使用されます。データ型や最大サイズは、既存データがあるときは変更に制限があります。
問題文中に示されたデフォルト値の変更は、既存データには影響しません。今後INSERT、UPDATEされるときに使用されます（正解c）。選択肢dのように既存レコードを更新したい場合は、明示的にUPDATE文を発行してください。
4．制約
表の列に「制約」を定義することで、格納されるデータに特定のルールを適用することができます。Oracleデータベースの表には次の制約を定義できます。
| 主キー | 表内の行を識別するために使用する列、または列の組み合わせであることの宣言。一意キー制約と同じ一意性の保証とNOT NULL制約と同じNULL値不可の制限が設定される |
|---|---|
| 一意キー | 一意であることを保証する宣言 |
| 外部キー | 参照表の参照キー列との間に参照関係があることを宣言。参照キーに存在する値またはNULL値のみが格納されることを保証する |
| チェック制約 | 条件式に対してTRUEとなる値のみが格納されることを宣言 |
| NOT NULL制約 | NULL値は格納されないことを宣言 |
主キー制約、一意キー制約を設定する場合、その列に索引が存在しなければ、自動的に一意索引が作成されます。各制約を定義するには、列を宣言すると同時に制約を設定する方法（列レベル）と、列宣言が完了した後で制約を設定する方法（表レベル）とがあります。
列レベル：
表レベル：
CONSTRAINT 制約名：制約に名前を付ける
制約定義：
主キー PRIMARY KEY
一意キー UNIQUE
外部キー FOREIGN KEY(外部キー列) REFERENCES 参照表(参照キー列)
チェック制約 CHECK(条件式)
NOT NULL制約 NOT NULL
制約を定義する場合は、次のことに注意します。
- NOT NULL制約は、列レベルでのみ宣言可能
- 主キー制約、一意キー制約、外部キー制約で複数列を使って宣言する場合は、表レベルでのみ宣言可能
- 制約名（CONSTRAINT句）を省略すると、「SYS_Cnnnn」という名前が付けられる
- 外部キー制約を宣言する場合、参照キー列には主キー制約または一意キー制約が宣言されている必要がある
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
上記の例では、empno列の主キー制約とename列のNOT NULL制約は列レベルで、sal列のチェック制約とdeptno列の外部キー制約は表レベルで宣言しています。制約に違反する行を格納することはできないため、DMLを実行すると、次のようにエラーメッセージが表示されます。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
問題1
制約の特徴として正しいものを3つ選択しなさい。
a．表の作成時にのみ宣言できる
b．表レベルで宣言できる
c．列レベルで宣言できる
d．表には必須である
e．表の削除を禁止することができる
正解：b、c、e
解説
制約は表作成時（CREATE TABLE文）、または表変更時（ALTER TABLE文）で定義できるデータのルールです（選択肢a）。列宣言と同時に宣言する方法と表定義が完成してから定義する方法があります（正解b、正解c）。表には必須というわけではありません。必要のある場合に宣言します（選択肢d）。
外部キー制約を使用する場合、格納するデータは参照表に存在している必要があります。さらに、外部キーに参照されている表は削除できないというルールが設定されます（正解e）。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
データが参照されている場合は、参照キーであるデータの更新（UPDATE）とレコードの削除（DELETE）が禁止されるというルールが設定されます。
問題2
次の文を確認してください。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
この文で自動的に索引が作成される列を2つ選択しなさい。
a．empno
b．ename
c．sal
d．ord_id
e．ord_status
f．ord_idとord_statusの複合
正解：a、f
解説
前回、宿題とした問題です。
主キー制約と一意キー制約を宣言するためには索引が必要です。表の作成と同時に制約を宣言する場合、該当列に自動的に一意索引が作成されます（正解a）。問題のように複数の列で制約を宣言した場合、その複数列で組み合わせた複合索引が作成されます（正解f）。
選択肢bと選択肢cのNOT NULL制約やチェック制約には自動的に索引は作成されません。問題にはありませんが、外部キー制約にも索引は作成されません。
選択肢dと選択肢eを選んでしまいますと、それぞれが個々の索引という意味になりますので注意しましょう。
このように実際の試験では、正解数を指定せず、「すべて選択」という出題がまれにあります。適切な選択肢をすべて選べるよう、内容を完全に把握する必要があります。
問題3
EMP表とEMP_GRADE表を作成し、結合できるようにする予定です。EMP表では、EMPNO列を主キーとして宣言しました。EMP_GRADE表にもEMPNO列を用意し、この列がEMP表を参照するようにします。EMP_GRADE表の宣言として正しいものを選択しなさい。
a．
CREATE TABLE emp_grade(
empno NUMBER(4),
grade VARCHAR2(10),
CONSTRAINT FOREIGN KEY (empno)
REFERENCES emp(empno));
b．
CREATE TABLE emp_grade(
empno NUMBER(4),
grade VARCHAR2(10),
CONSTRAINT emp_grade_fk FOREIGN KEY (empno)
REFERENCES emp(empno));
c．
CREATE TABLE emp_grade(
empno NUMBER(4),
grade VARCHAR2(10),
CONSTRAINT emp_grade_fk FOREIGN KEY (empno)
REFERENCES (empno));
d．
CREATE TABLE emp_grade(
empno NUMBER(4),
grade VARCHAR2(10),
CONSTRAINT emp_grade_fk FOREIGN KEY emp(empno)
REFERENCES (empno));
正解：b
解説
外部キー制約の宣言は、制約の中で一番面倒なものかもしれません。今回の問題で使用しているように表制約構文を使用する場合のポイントは、「FOREIGN KEY句で外部キー列を指定し、REFERENCES句で参照表と参照キー列を指定する」ことです。制約名を指定する場合は「CONSTRAINT 制約名」を付ければ完成です（正解b）。
列制約構文の場合は、列の宣言と同時に制約を宣言するわけですから、FOREIGN KEY句は必要ありません。
不正解の選択肢のポイントは次のとおりです。
●選択肢a：制約名が不足しています。制約名の指定は「CONSTRAINT 制約名」です。
●選択肢c：参照表名が不足しています。参照表と参照キーの指定は「REFERENCES 参照表名(参照キー列名)」です。
●選択肢d：外部キー列の指定は「FOREIGN KEY(外部キー列名)」、参照表と参照キーの指定は「REFERENCES 参照表名(参照キー列名)」です。
宿題
次回は、今回に引き続きオブジェクトの作成と管理について確認します。次の宿題を解いておいてください。
シノニムを作成することが有効なものを2つ選択しなさい。
a．検索パフォーマンスを向上させたい場合
b．表名が複雑な場合
c．権限を与えたくない場合
d．ほかのスキーマのオブジェクトにアクセスする場合
e．アクセス制限をしたい場合
Copyright © ITmedia, Inc. All Rights Reserved.