TOPスキーマ・オブジェクト >表・制約について


表・制約について


表とは

表は、Oracleにおけるデータ記憶の基本単位です。データは、行と列の構造で格納されます。
表の各列ではデータ型が決められています。表の各行にはデータ型にあった値が入力されます。 これは、表の中のデータとして正しくないものが入力されてしまうことを防ぐ働きがあります。 またデータ型だけではチェックしきれないものは、表の制約でチェックすることもできます。

表の定義

表を作成する場合以下の項目を定義する必要があります。


データ型

データ型の種類には文字型、数値型、日付型、バイナリ型があります。

文字型

CHAR型は長さが固定された文字列データに用います。列を定義する場合は長さをバイト数で指定します。 長さを省略した場合は1バイトの文字列になります。
INSERTやUPDATE時に列定義よりも短い文字列が入力された場合は、空白が後ろに埋め込まれます。 CHAR(100)と定義した列に1文字入力すると、残りの99バイトに空白が埋められて100バイトの領域が確保されます。

VARCHAR2型は可変長文字列です。CHAR型とは異なり、INSERT時等に空白が埋め込まれることはありません。 VARCHAR2(100)と定義した文字列に1文字入力すると、1バイトの領域のみ確保されます。

文字列を比較する場合、CHAR型とVARCHAR2型では異なる結果になる事があるので注意が必要です。

  WHERE MOJI_COL = ‘A’

MOJI_COL 列に’A ‘(先頭Aと空白4個)というデータがあった場合、結果はどうなるでしょうか。それはMOJI_COL列のデータ型に依存します。 CHAR型であれば等号は成立し、VARCHAR2型であれば等号は不成立となります。

文字列同士の比較では、両方とも固定長であれば、比較前に長さの短い文字列の後ろに空白を埋め込んで長さを等しくします。
そのため’A ‘と’A’は等しいと判断されます。
一方が可変長であれば、空白埋めはなされないので、’A ‘と’A’は異なる文字列であると判断されます。

LONG型は可変長文字列でバイト数は指定しません。しかし、同じ可変長文字列であるVARCHAR2と異なり、多くの制限が設けられています。 この制限は後述するLONG ROW型にもあてはまります。

表1:文字型データ概要

定義 概要
CHAR(s) 固定長の文字データ。
sは1から2000バイトまで。(s)のデフォルト値は(1)
VARCHAR2(s) 可変長の文字データ。sは1から4000バイトまで。(s)は省略不可。
LONG 可変長の文字データ。最大長は2ギガバイトまで。

数値型

NUMBER型には数値データが入力されます。列の定義はNUMBER(p, s)と記述します。
pは最大精度で、1から38桁までです。sは小数点以下の桁数を指定します。sを省略すると最大p桁の整数なり、 p, s共に省略すると、最大精度38桁の浮動小数点となります。またsに負の数を指定すると、 s桁以下を丸めた数値となります。(表2参照)

表2:NUMBER型の定義と結果

定義 定義内容 データ格納結果
NUMBER( 9, 2 ) 全部で9桁小数点以下2桁の数値 1234567.89
NUMBER( 9 ) 全部で9桁小数点以下0桁の数値 1234567
NUMBER( *, 2 ) 全部で38桁小数点以下2桁の数値 1234567.89
NUMBER(9, -2 ) 全部で9桁10の位以下を丸めた数値 1234500
NUMBER 全部で38桁の浮動小数点 1234567.89
NUMBER( 7,2 ) 全部で7桁小数点以下2桁の数値 精度を超えるため格納不可
NUMBER( 7,-2 ) 全部で7桁10の位以下を丸めた数値 1234500
NUMBER( 6 ) 全部で6桁小数点以下0桁の数値 精度を超えるため格納不可

*格納するデータはいずれも1234567.89とする

日付型

DATE型は日付と時刻がデータとして入力されます。日付だけ時刻だけのデータではなく、 年月日時分秒まで入力されます。データ長は7バイトの固定長です。

バイナリ型

RAW型は可変長バイナリデータ、LONG RAW型はバイト数を指定しない可変長バイナリデータです。 LONG RAWは前述のLONG型と同様の制限を有します。

表3:バイナリ型データ概要

定義法 概要
RAW(s) 可変長のバイナリデータ。sは1から2000バイトまで。(s)は省略不可。
LONG RAW 可変長のバイナリデータ。

以上がORACLE7までにも存在したデータ型です。 ORACLE8では更に、4ギガバイトまでサポートする文字データ型のCLOB型や、バイナリデータ型のBLOB型、 ファイルシステムへのポインタとなるデータが入力されるBFILE型が追加されました。


制約とは

データベースを使用してデータを管理する場合、データは規則に基づいて、常に正しい情報として格納されなければなりません。 データや、データ同士の関係が正しい状態であることをデータ整合性が保たれている、といいます。

整合性を保証する方法には、表に定義を埋め込む「宣言型」と、プログラムでデータをチェックする「手続き型」の方法があります。 制約は宣言型に含まれます。

制約には表に格納されるデータのルールであるエンティティ整合性制約と表同士の関連のルールである参照整合性制約の2つが存在します。

エンティティ整合性制約

エンティティ整合性制約には以下のものがあります。

NOT NULL制約
指定した列に対して、NULL値の入力を禁止する。必ずNULL以外の値を入力しなければならない。
UNIQUE制約
指定した列(または列の組み合わせ)に対して、同じ値の入力を禁止する「一意キー制約」とも呼ぶ。
PRIMARY KEY制約
表に格納されたデータを一意に識別するための、主キー列(または列の組み合わせ)を設定する。 自動的に「UNIQUE」で「NOT NULL」な設定になる。「主キー制約」とも呼ぶ。 PRIMARY KEYは1つの表に対して1つしか設定できない。
CHECK制約
明示的に条件を定義する。定義した条件を満たす値のみ入力を許される。

参照整合性制約

値を参照する側の表を子表、または依存表と呼び、関連する値を持つ列のことを外部キーと呼びます。 値が参照される側の表は親表、または参照表と呼び、参照される値を持つ列のことを親キーと呼びます。 親キーには、主キーや一意キーを指定します。

親表の親キーと子表の外部キーに格納される値の整合性を保つのが、参照整合性制約(又はFOREIGN KEY制約)です。 即ち、参照整合性制約が設定されていれば、子表にデータを入力するときに自動的に親キーを参照し、 親キーに格納されていない値の入力が禁止されます。

参照整合性制約を定義した表を作成する場合、事前に親表が存在し、かつ親キーが主キー、 または一意キーである必要があります。但し、NULLを入力することは可能です。 NULLの入力を制限したい場合は、NOT NULL制約を追加する必要があります。

列制約と表制約

制約は定義の方法により、列制約と表制約に分類されます。 列制約は列のデータ型と同様に列の定義の中で制約を指定する方法です。表制約は列の定義とは別の場所で、表に対して制約を行います。

この2つには次のような違いがあります。

デフォルト

データ型や制約とは別に、列のデフォルト値を設定することも可能です。 表にデータを入力する場合に、値の指定がなかった列にはデフォルト値が入力されます。 デフォルト値の設定がなされていない列は、NULLがデフォルト値となります。

各制約の書式については表4に記します。

表4:制約とデフォルトの書式

制約名  制約法  書式
NOT NULL制約 列制約 列名 データ型 [CONSTRAINT 制約名] NOT NULL
一意キー制約 列制約 列名 データ型 [CONSTRAINT 制約名] UNIQUE
表制約 [CONSTRAINT 制約名] UNIQUE (列名 [, 列名]...)
主キー制約 列制約 列名 データ型 [CONSTRAINT 制約名] PRIMARY KEY
表制約 [CONSTRAINT 制約名] PRIMARY KEY (列名 [, 列名]...)
チェック制約 列制約 列名 データ型 [CONSTRAINT 制約名] CHECK (条件)
表制約 [CONSTRAINT 制約名] CHECK (条件)
参照整合性制約 列制約 列名 データ型 [CONSTRAINT 制約名] REFERENCES 親表名 (親キー列名)
表制約 [CONSTRAINT 制約名] FOREIGN KEY (外部キー列名 [, 外部キー列名]...)
REFERENCES 親表名 (親キー列名 [, 親キー列名]...)
デフォルト 列名 データ型 DEFAULT 値

CONSTRAINT 制約名を省略した場合は、ORACLEが「 C_ 」で始まる名前を自動的に命名します。


表の作成・削除・変更

表の作成

表の作成はCREATE TABLEで行います。構文は以下の通りです。表領域、記憶領域管理パラメータについての説明は割愛します。

CREATE TABLE 表名
 ( 列名 データ型 [ [CONSTRAINT 制約名] 制約]
 [, 列名 データ型 [ [CONSTRAINT 制約名] 制約] ]
 ...)
[TABLESPACE 表領域名]
[記憶領域管理パラメータ]

表を作成するとき、指定する表名や列名には以下のような規則があります。

表の削除

表の削除にはDROP TABLEを使用します。DROP TABLEでは削除したい表の名前を指定します。 CASCADE CONSTRAINTSを指定すると削除する表を親として参照している子表の制約も一緒に削除します。 構文は以下の通りです。

DROP TABLE 表名
[CASCADE CONSTRAINTS]

表名の変更

表名を変更するにはRENAMEを使用します。RENAME文では、変更したい表の名前と、変更後の名前を指定します。

RENAME 古い表名 TO 新しい表名

表の変更

表の内容を変更するには、ALTER TABLEを使用します。列や制約の追加、変更、削除などは、ALTER TABLEで行います。

列の追加

ALTER TABLE 表名
ADD (列名 データ型 [, 列名 データ型]...)

列定義の変更

ALTER TABLE 表名
MODIFY (列名 データ型 [, 列名 データ型]...)

列の削除は行えません。

制約の追加

ALTER TABLE 表名 ADD 表制約構文

制約の削除

ALTER TABLE 表名 DROP CONSTARINT 制約名

制約の変更は行えません。

制約を削除せずに、一時的に制約を無効にすることも可能です。

制約の無効化

ALTER TABLE 表名 DISABLE 制約名

制約の有効化

ALTER TABLE 表名 ENABLE 制約名

テーブル・制約の確認

ユーザーが作成したテーブルや制約を確認するには、データディクショナリーを参照します。
USER_TABLESは全ユーザーが使用できます。現在使用しているユーザーの表の名前や、さらに詳細な設定情報などが 確認できます。USER_TABLESの列構成は表5のようになっています。

USER_CONSTRAINTSでは、制約の名前や種類、制約を設定した表などが確認できます。 USER_CONSTRAINTSの列構成は表6のようになっています。

表5:USER_TABLESの列構成

列名 概要
TABLE_NAME 表名
TABLESPACE_NAME 表が格納されている表領域の名前
CLUSTER_NAME 表が属するクラスタがある場合、そのクラスタの名前
IOT_NAME 表が索引構成表のオーバーフロー・エントリの場合、その索引構成表の名前
PCT_FREE データ・ブロックに設定された空き領域の最小の割合(パーセント単位)
PCT_USED データ・ブロックに設定された使用領域の最小の割合(パーセント単位)
INI_TRANS トランザクション・エントリの初期数
MAX_TRANS トランザクション・エントリの最大数
INITIAL_EXTENT 初期エクステントのサイズ(バイト単位)
NEXT_EXTENT 第2エクステントのサイズ(バイト単位)
MIN_EXTENTS エクステントの最小数
MAX_EXTENTS エクステントの最大数
PCT_INCREASE エステント・サイズの増加の割合(パーセント単位)
FREELISTS プロセス空きリストの数
FREELIST_GROUPS 空きリスト・グループの数
LOGGING ロギングが使用可能かどうか
BACKED_UP 前回の変更以降に表がバックアップされているかどうか
NUM_ROWS 表内のデータの行数
BLOCKS 表内の使用されたデータ・ブロック数
EMPTY_BLOCKS 表内の未使用のデータ・ブロック数
AVG_SPACE 表内の平均の使用可能な空き領域
CHAIN_CNT 表内の連鎖行数
AVG_ROW_LEN 行のオーバーヘッドを含めた行の平均の長さ
AVG_SPACE_FREELIST_BLOCKS 空きリストのすべてのブロックの平均の空き領域
NUM_FREELIST_BLOCKS 空きリストのブロック数
DEGREE 全表走査に使用される問合せサーバー数
INSTANCES 表がいくつかのインスタンスにまたがって走査される場合の、インスタンスの数
CACHE 表がSGA内のデータベース・バッファ・キャッシュにキャッシュされるかどうか
TABLE_LOCK 表がENABLED、DISABLEDのどちらであるか
SAMPLE_SIZE この表の分析で使用されるサンプル・サイズ
LAST_ANALYZED この表が最後に分析された日付
PARTITIONED この表がパーティション化されているかどうか
IOT_TYPE 表が索引構成表の場合、索引構成表の種類。索引構成表なのかオーバーフロー・エントリなのか
TEMPORARY 一時的な表かどうか
NESTED ネストした表かどうか
BUFFER_POOL オブジェクトに指定したデフォルトのバッファ・プールの種類

表6:USER_CONSTRAINTSの列構成

列名 概要
OWNER 制約の所有者
CONSTRAINT_NAME 制約の名前
CONSTRAINT_TYPE 制約のタイプ
C(CHECK制約、NOT NULL制約)
P(PRIMARY KEY制約)
U(UNIQUE制約)
R(参照整合性制約)
V(ビューでのチェック・オプション付き)
TABLE_NAME 制約が指定されている表の名前
SEARCH_CONDITION CHECK制約の検査条件
R_OWNER 参照整合性制約で使用される表の所有者
R_CONSTRAINT_NAME 親表の一意性制約定義の名前
DELETE_RULE 参照整合性制約の削除ルール: CASCADE、NO ACTION
STATUS 制約の状態: ENFORCEDまたはENABLED、DISABLED
DEFERRABLE 制約が延期可能かどうか
DEFERRED 制約が最初に延期されたかどうか
VALIDATED すべてのデータが制約に従っているかどうか(VALIDATED、NOT VALIDATED)
GENERATED 名前がOracleによって命名されたかどうか
LAST_CHANGE 制約が最後に使用可能または使用禁止にされた時点



[サンプルSQL]

TOPスキーマ・オブジェクト >表・制約について

2001/03/02 担当:T.I