Oracle 表の作成と管理

• データベースオブジェクト
  • 表やビュー，索引などのデータベースの中で扱われるものをデータベースオブジェクトと呼ぶ
  • 具体的には，表，ビュー，索引，シノニム，順序(シーケンス)

表の作成

• テーブル名は長さ30バイト以内

DEFAULT オプション

• デフォルト値には，リテラル，式，またはSQL関数などを使用することができる．
• 他の列名や擬似列は使用できない

データ型

文字型

• CHAR
  • 固定長の文字データ型．2000バイトまで．未使用の部分は空白が充?される．

• VARCHAR2
  • 可変長の文字データ型1〜4000バイト．

• LONG
  • 可変長の文字データ型．2GBまでテキストデータを格納できる．
  • 表ごとに1つの列しか使用できず，WHERE/GROUP BY では使用できない．
  • 整合性制約や索引に使用できない．
  • サブクエリーを使用して表を作成する場合，LONG型の列はコピーされない．

数値型

• NUMBER(p, s)
  • 最大精度は３８桁．sは位取り（小数点以下の桁数）を指定する．精度と位取りを省略すると，最大38桁の浮動小数点として使用可能．sを省略すると，pの精度の整数として使用可能．

日付型

• 日付，時間を含む固定長7バイトの日付データ型．

バイナリ型

• RAW(s)
• sは１〜２０００バイト．可変長のバイナリ型．

• LONG RAW
  • 最大２Gバイトまでの可変長バイナリ．
  • テーブルごとに1つの列しかLONG型は使用できず，WHERE/GROUP BY も使用できない．
  • 整合性制約や索引には使用できない．
  • 副問い合わせを使用して表を作成する場合は，LONG型の列はコピーされない．

LOG型

• LONG型と異なり表の複数の列の定義が可能．
• 表と同じ表領域，または異なる表領域に格納でき，効果的なデータのアクセスが可能となる．

• BLOB
  • バイナリデータ型．最大４Gバイト．

• CLOB
  • 文字データ型．最大４Gバイト．

• BFILE
  • 読み取り専用のバイナリデータ型．
  • BFILEはOSファイルとして保存されているデータにアクセスできる．最大サイズは4GBまで．

ROWID型

• ROWID
  • 表の中の行の一意なアドレスを表すBASE64文字列．ROWID擬似列から戻される値に使用する．

日時および期間型

• TIMESTAMP
  • 時刻を1秒未満の単位として核のうできる．
  • TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE の3種類がある．

• TIMESTAMP[(p)]
  • DATE型の拡張．年，月，日，時，分，秒，少数秒値を格納する．pは少数秒の桁数を０〜９の範囲で指定する．

• TIMESTAMP[(p)] WITH TIME ZONE
  • タイムゾーンン変位を含む.

• TIMESTAMP[(p)] WITH LOCAL TIME ZONE
  • タイムゾーン変位を含む．タイムゾーン．データベースのタイムゾーンになる．

• INTERVAL YEAR[(y)] TO MONTH
  • 時間間隔(年月)を格納する．y はYEARの日時フィールドの桁数を指定する．デフォルトは2．

• INTERVAL DAY[(d)] TO SECOND[(s)]
  • 時間間隔(日，時，分，秒)を格納する．d は DAY(日時)日時フィールドの桁数を０〜９の範囲で指定する．デフォルトは２．

制約

• 列制約と表制約構文がある．

エンティティ整合性制約

• データ単体の整合性制約

• NOT NULL制約
  • 列制約構文．NULLを許可しない．

• 一意制約
  • 指定された列または列の組み合わせに同じ値が入力されることを禁止する
  • 複数の列の組み合わせた複合一意制約は表制約構文にのみ指定可能．
  • 同じ列にNOT NULLが定義されていない限り，1つ以上のNULL値を入力できる．

• 主キー制約
  • 表内の行データを一意に特定できる列または列の組み合わせの中で主たるものを主キーという．
  • 主キー制約には自動的に一意制約とNOT NULL制約が付加される．
  • 複数の列を組み合わせた複合種キーは表制約構文でのみ指定可能．

• チェック制約
  • 明示的に入力条件を定義し，条件を満たすデータのみ入力可能とする．1つの列で複数のチェック制約を定義する事が可能．
  • 問い合わせの条件と同じ要素を使用できるが，以下のものは使用できない．
    • CURRVAL, NEXTVAL, LEVEL, ROWNUM擬似列の参照
    • SYSDATE, UI, USER, USERENV関数の使用
    • 別の行の値を参照する問い合わせ

• Tips
  • 一意制約，主キー制約が設定されると，その列に対して索引が存在しない場合，自動的に索引が作成される．

外部キー(参照整合性)制約

• 表と表の間の関係づけを保つために使用する
• 子表の参照列のことを外部キー(FOREIGN KEY)といい，親表の参照される列を親キーという．
• 親キーはREFERENCESを用いて指定する．

CONSTRAINT FK_xxx_child_field REFERENCES parent_table(parent_field)

• 親キーは主キーまたは一意キーのいずれかでないといけない．
• 複数の列を組み合わせた複合外部キーは表制約構文のみで指定可能．

• ON DELETE CASCADE
  • 親表内の行が削除された場合に子表の依存行も削除される．

• ON DELETE SET NULL
  • 親表内の行が削除されたときに，依存する子表の外部キーの値をNULLにセットする．

• ON DELETE CASCADEオプション，ON DELESET SET NULLオプションのいずれもをセットしないときはRESTRICT規則と呼ばれ，子表から参照される親表内の行を削除できない．

既存表から新しい表の作成

• CREATE TABLE AS コマンドによりできる．
• 列の名前，行データ，NOT NULL制約のみがコピーされる．
• NOT NULL 以外の制約やトリガーはコピーされないので注意が必要．

CREATE TABLE table_name [(row_name1, row_name2, row_name3)] AS
SELECT row1, row2, row3 FROM table_name_src WHERE row1 = some_value;

• 既存表からの表の作成について以下のポイントを留意すること．
  • 列定義には，列名，デフォルト値，整合性制約を含める事ができる．
  • 列を指定する場合，副問い合わせのSELECT リストの列数と列数を一致させなければならない．
  • 表は指定された列名で作成され，副問い合わせによって取り出された行が表に挿入される
  • 列を指定しない場合，表列名は，副問い合わせのSELECTリストの列名と同じ
  • 副問い合わせのSELECTリストに式を指定する場合は，列別名を指定する．
  • NOT NULL制約とデータ型のみコピーされる
  • NOT NULL以外の制約はコピーされない．