SQLで表のデータを制限する：ORACLE MASTER Bronze　SQL基礎I 講座（11）

Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！

» 2006年03月10日 00時00分公開

[有限会社G.F.インフィニティ，＠IT]

　前回「SQLでデータを自在に操作する」で、データ操作を紹介しました。今回から2回にわたり、オブジェクトの作成と管理について学びましょう。

ORACLE MASTER Bronze　SQL基礎I 講座　各回のインデックス

理解しておきたいこと

1．確認しておきたい内容

表の作成
制約の作成および保守

2．基本のデータベースオブジェクト

　データベースで扱うオブジェクトはさまざまですが、基本のデータベースオブジェクトには次のものがあります。


表	データを格納する基本となるオブジェクト。定義として列を持ち、行を格納する
ビュー	1つ以上の表データに対する問い合わせ結果を表示するための論理的な定義。SELECT文に名前を付けたもの
シノニム	オブジェクトに対する別名
索引	問い合わせのパフォーマンスを向上させるため、列値と行へのポインタを格納したもの
順序	一意的な番号を生成するもの

　どのようなときにどのオブジェクトを使用するかを把握しておきましょう。

3．表の作成

　表を作成するには、事前に表を作成する権限（CREATE TABLE権限）と領域割り当て（QUOTA）が必要です。

CREATE TABLE 表名 (列名データ型 [DEFAULT デフォルト値][, ...]);

データ型：列のデータ型と最大サイズ
DEFAULT：列のデフォルト値。列に値が格納されないときに使用される

　副問い合わせを使用し、別の表から定義をコピーして表を作成することもできます。

CREATE TABLE 表名 AS SELECT 列名リスト FROM 表名 [WHERE 条件式];

　表名や列名は分かりやすいものを使用しましょう。次のような命名規則があります。

文字から始める（数字から始めることはできない）
長さは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の列の数とデータ型に一致する必要があります。

INSERT INTO 表名 [(列名1, 列名2, ...)] SELECT 列名1, 列名2 FROM 表名;

例：
SQL> CREATE TABLE tab1(col1 NUMBER(2), col2 VARCHAR2(5)
                                            DEFAULT 'TEST');
SQL> INSERT INTO tab1 VALUES(10, DEFAULT);
SQL> SELECT * FROM tab1;
      COL1 COL2
---------- -----
        10 TEST

問題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データ型は日、時間、分、秒（小数点以下も含める）による間隔データを扱います。

SQL> CREATE TABLE test(c1 TIMESTAMP);
SQL> INSERT INTO test VALUES(SYSDATE);
SQL> INSERT INTO test VALUES(SYSTIMESTAMP);
SQL> SELECT * FROM test;
C1
------------------------------------------------------
06-03-26 16:44:59.000000
06-03-26 16:44:59.620000

　1行目はSYSDATE関数の結果です。SYSDATE関数はDATEデータ型のため、秒の小数部分は格納されません。2行目のSYSTIMESTAMP関数はTIMESTAMPデータ型のため、秒の小数部分も格納されます。

SQL> SELECT c1 + INTERVAL '10-6' YEAR TO MONTH FROM test;
C1+INTERVAL'10-6'YEARTOMONTH
---------------------------------------------------------
16-09-26 16:44:59.000000000
16-09-26 16:44:59.620000000

　間隔データを使用すると、より高度な加算／減算などができます。上記の例では10年6カ月を加算しています。

　選択肢bのような間隔データはありません。正しく覚えておきましょう。

問題2

次の定義を確認してください。

ORD_ID
CUST_NAME
ORD_STATUS
ORD_DATE

数値データ
文字データ
文字データ
日付データ

注文番号
顧客名
注文ステータス
注文日

注文日に値がセットされないときは今日の日付が格納されるように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

次の文を確認してください。

ALTER TABLE emp MODIFY (sal DEFAULT 1000);

この文の結果として正しいものを選択しなさい。

　a．デフォルト値は列の変更では定義できないため、エラーとなる

　b．数値データを変更することはできないため、エラーとなる

　c．デフォルト値は、次回以降の更新で使用される

　d．既存レコードの該当する列値がデフォルト値で更新される

正解：c

解説

　表の定義を変更するには、ALTER TABLE文を使用します。ALTER TABLE MODIFY文では列の定義を変更できます。列のデータ型や最大サイズ、デフォルト値の定義やNOT NULL制約の設定／解除に使用されます。データ型や最大サイズは、既存データがあるときは変更に制限があります。

　問題文中に示されたデフォルト値の変更は、既存データには影響しません。今後INSERT、UPDATEされるときに使用されます（正解c）。選択肢dのように既存レコードを更新したい場合は、明示的にUPDATE文を発行してください。

UPDATE emp SET sal=DEFAULT;

4．制約

　表の列に「制約」を定義することで、格納されるデータに特定のルールを適用することができます。Oracleデータベースの表には次の制約を定義できます。


主キー	表内の行を識別するために使用する列、または列の組み合わせであることの宣言。一意キー制約と同じ一意性の保証とNOT NULL制約と同じNULL値不可の制限が設定される
一意キー	一意であることを保証する宣言
外部キー	参照表の参照キー列との間に参照関係があることを宣言。参照キーに存在する値またはNULL値のみが格納されることを保証する
チェック制約	条件式に対してTRUEとなる値のみが格納されることを宣言
NOT NULL制約	NULL値は格納されないことを宣言

　主キー制約、一意キー制約を設定する場合、その列に索引が存在しなければ、自動的に一意索引が作成されます。各制約を定義するには、列を宣言すると同時に制約を設定する方法（列レベル）と、列宣言が完了した後で制約を設定する方法（表レベル）とがあります。

列レベル：

CREATE TABLE 表名 (列名データ型 [DEFAULT デフォルト値][CONSTRAINT 制約名] 制約定義[, ...]);

表レベル：

CREATE TABLE 表名 (列名データ型 [DEFAULT デフォルト値[, ...],

[CONSTRAINT 制約名] 制約定義(制約定義列)[, ...]);

CONSTRAINT 制約名：制約に名前を付ける
制約定義：
主キー　PRIMARY KEY
一意キー　UNIQUE
外部キー　FOREIGN KEY(外部キー列) REFERENCES 参照表(参照キー列)
チェック制約　CHECK(条件式)
NOT NULL制約　NOT NULL

　制約を定義する場合は、次のことに注意します。

NOT NULL制約は、列レベルでのみ宣言可能
主キー制約、一意キー制約、外部キー制約で複数列を使って宣言する場合は、表レベルでのみ宣言可能
制約名（CONSTRAINT句）を省略すると、「SYS_Cnnnn」という名前が付けられる
外部キー制約を宣言する場合、参照キー列には主キー制約または一意キー制約が宣言されている必要がある

例：
SQL> CREATE TABLE emp2(
  2    empno   NUMBER(4) CONSTRAINT emp2_pk PRIMARY KEY,
  3    ename   VARCHAR2(15) NOT NULL,
  4    sal     NUMBER(7,2),
  5    deptno  NUMBER(2),
  6  CONSTRAINT emp_sal_chk CHECK(sal > 0),
  7  CONSTRAINT emp_fk FOREIGN KEY (deptno)
                                REFERENCES dept(deptno));

　上記の例では、empno列の主キー制約とename列のNOT NULL制約は列レベルで、sal列のチェック制約とdeptno列の外部キー制約は表レベルで宣言しています。制約に違反する行を格納することはできないため、DMLを実行すると、次のようにエラーメッセージが表示されます。

SQL> INSERT INTO emp2 VALUES(8000, 'NAME', 2000, 50);
INSERT INTO emp2 VALUES(8000, 'NAME', 2000, 50)
*
行1でエラーが発生しました。:
ORA-02291: 整合性制約(SCOTT.EMP_FK)に違反しました
                                             - 親キーがありません
SQL> INSERT INTO emp2 VALUES(8000, 'NAME', 2000, 30);
SQL> UPDATE emp2 SET ename=NULL WHERE empno=8000;
UPDATE emp2 SET ename=NULL WHERE empno=8000
                *
行1でエラーが発生しました。:
ORA-01407: ("SCOTT"."EMP2"."ENAME")をNULLには更新できません
SQL> UPDATE emp2 SET sal=-100 WHERE empno=8000;
UPDATE emp2 SET sal=-100 WHERE empno=8000
*
行1でエラーが発生しました。:
ORA-02290: チェック制約(SCOTT.EMP_SAL_CHK)に違反しました
SQL> INSERT INTO emp2 VALUES(8000, 'NAME2', 3000, 30);
INSERT INTO emp2 VALUES(8000, 'NAME2', 3000, 30)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(SCOTT.EMP2_PK)に反しています

問題1

制約の特徴として正しいものを3つ選択しなさい。

　a．表の作成時にのみ宣言できる

　b．表レベルで宣言できる

　c．列レベルで宣言できる

　d．表には必須である

　e．表の削除を禁止することができる

正解：b、c、e

解説

　制約は表作成時（CREATE TABLE文）、または表変更時（ALTER TABLE文）で定義できるデータのルールです（選択肢a）。列宣言と同時に宣言する方法と表定義が完成してから定義する方法があります（正解b、正解c）。表には必須というわけではありません。必要のある場合に宣言します（選択肢d）。

　外部キー制約を使用する場合、格納するデータは参照表に存在している必要があります。さらに、外部キーに参照されている表は削除できないというルールが設定されます（正解e）。

SQL> DROP TABLE dept;
DROP TABLE dept
           *
行1でエラーが発生しました。:
ORA-02449:
表の一意キーまたは主キーが外部キーに参照されています

　データが参照されている場合は、参照キーであるデータの更新（UPDATE）とレコードの削除（DELETE）が禁止されるというルールが設定されます。

問題2

次の文を確認してください。

CREATE TABLE orders(
  empno       NUMBER(4)         UNIQUE,
  ename       VARCHAR2(15)      NOT NULL,
  sal         NUMBER(7,2)       CHECK(sal>0),
  ord_id      NUMBER(3),
  ord_status  CHAR(2),
  CONSTRAINT orders_pk PRIMARY KEY(ord_id, ord_status));

この文で自動的に索引が作成される列を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．アクセス制限をしたい場合

「次回」へ

「ORACLE MASTER Bronze　SQL基礎I 講座」バックナンバー

SpecialPR

スポンサーからのお知らせPR

SpecialPR

＠IT eBook

システム開発ノウハウ【発注ナビ】PR

編集部からのお知らせ

12/9【無料オンラインセミナー】『＠IT 運用管理セミナー 2024 秋　今こそ見直したい運用管理者の現状と未来』アクセンチュア AIセンター／シニアフェロー南昌宏氏による【基調講演　生成AI／AIは「運用管理」をどう変える？～既に変わりつつある「運用の価値」「管理者の役割」～】、ITプレナーズジャパン・アジアパシフィック最上千佳子氏による【基調講演　IT運用が経営を変える！サービスマネジメントの新潮流】

あなたにおすすめの記事PR

@ITについて

RSSについて

＠ITのRSS一覧

アイティメディアIDについて

アイティメディアIDとは

メールマガジン登録

＠ITのメールマガジンは、もちろん、すべて無料です。ぜひメールマガジンをご購読ください。

申し込みページへ

ITmediaはアイティメディア株式会社の登録商標です。

SQLで表のデータを制限する：ORACLE MASTER Bronze　SQL基礎I 講座（11）

ORACLE MASTER Bronze　SQL基礎I 講座　各回のインデックス

理解しておきたいこと

1．確認しておきたい内容

2．基本のデータベースオブジェクト

3．表の作成

問題1

解説

問題2

解説

問題3

解説

4．制約

問題1

解説

問題2

解説

問題3

解説

宿題

＠IT eBook

ユーザーの「無知」は罪なのか？――IT訴訟解説ベンダーvs.ユーザー企業死闘編

Pythonで日付を扱う際の「はて？」を解決する無料の電子書籍『解決！Python　日付データ編』

「このままゼロトラストへ進んでいいの？」と迷う企業やこれから入門する企業も必見、ゼロトラストの本質、始め方／進め方が分かる無料の電子書籍

超IT用語解説漫画「食べ超」を読んで、テクノロジーの進化に思いをはせよう

編集部からのお知らせ

SQLで表のデータを制限する：ORACLE MASTER Bronze SQL基礎I 講座（11）

ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス

理解しておきたいこと

1．確認しておきたい内容

2．基本のデータベースオブジェクト

3．表の作成

問題1

解説

問題2

解説

問題3

解説

4．制約

問題1

解説

問題2

解説

問題3

解説

宿題

＠IT eBook

ユーザーの「無知」は罪なのか？――IT訴訟解説 ベンダーvs.ユーザー企業 死闘編

Pythonで日付を扱う際の「はて？」を解決する無料の電子書籍『解決！Python 日付データ編』

「このままゼロトラストへ進んでいいの？」と迷う企業やこれから入門する企業も必見、ゼロトラストの本質、始め方／進め方が分かる無料の電子書籍

超IT用語解説漫画「食べ超」を読んで、テクノロジーの進化に思いをはせよう

編集部からのお知らせ

SQLで表のデータを制限する：ORACLE MASTER Bronze　SQL基礎I 講座（11）

ORACLE MASTER Bronze　SQL基礎I 講座　各回のインデックス

ユーザーの「無知」は罪なのか？――IT訴訟解説ベンダーvs.ユーザー企業死闘編

Pythonで日付を扱う際の「はて？」を解決する無料の電子書籍『解決！Python　日付データ編』