表の作成と管理 - m2 @Wiki

• 10.1 スキーマオブジェクト

データベースの中で定義・作成され、利用されるものをオブジェクトと呼ぶ。
オブジェクトはスキーマオブジェクトと非スキーマオブジェクトの2種類に分けられる。

【スキーマオブジェクト】
特定のユーザーによって所有される。データの格納や検索のために直接的に使われる。

1. 表
2. インデックス
3. ビュー
4. シノニム　：オブジェクトに対する別名。

　　→名前を簡略化したり、オブジェクトの所有者を隠す。

1. 順序　　　：一意な値を生成する。
2. データベースリンク：リモートデータベースにアクセスするために使われる。

【非スキーマオブジェクト】
特定のユーザーには所有されず、システム全体で共用される。

1. 記憶域
2. ローカル
3. ユーザー など

• 10.2 オブジェクトのネーミング規則

1. 使用できる文字はアルファベット、数字、_、$、#

　→日本語環境であれば漢字、カタカナ、ひらがなも使用可能

1. 名前は必ず文字で開始する（数字や_、$、#は不可)
2. 名前の長さは30Bytesまで。
3. スキーマの中で一意でなければならない

　→スキーマが異なれば、同じ名前でも可。

1. Oracleの予約語は使用不可

• 10.3 CREATE TABLE必要要件

1. CREATE TABLE権限(自分のスキーマに自分所有の表を作成可能)

→CREATE ANY TABLE権限(他のスキーマに他ユーザー所有する表を作成可能)

1. 記憶域(表を格納する)

• 10.4 CREATE TABLE文 構文

CREATE TABLE [スキーマ名].表名
(列名 データ型 [DEFAULT 式] [制約])
[TABLESPACE 表領域名]
[領域管理パラメータ]
＜次の項目を指定可能＞

1. スキーマ
2. 列のデータ型
3. 制約

★シノニムは指定できない

→CREATE SYNONYM文で定義する

• 10.5 SQL文でのスキーマ名デフォルト値

SQL文でスキーマオブジェクトを指定する際にスキーマ名を省略すると、ログオンユーザー名がデフォルトで使われる。

• 10.6 表名と列名の一致は可能か？

可能。表名と列名が同一でも問題なし。

• 10.7 オブジェクト名の大文字・小文字区別

オブジェクトの名前は大文字と小文字が区別されない。
小文字を使う場合、ダブルクォーテーション(")で名前を囲う。
また、以下の場合にも(")で名前を囲う。

1. 空白を含む名前
2. 数字、#、_、$で始まる名前
3. 英数字、#、_、$以外の文字を含む名前
4. 予約語を名前として使用する場合

• 10.8 Oracle予約語

DATE：予約語
DATA：予約語でない

• 10.13 VARCHAR2(n)について

1. nを超えるデータはエラーになる。
2. nは省略不可。
3. 1～4000バイトまで格納できる。

• 10.14 CHAR(n)について

1. nを超えるデータはエラーになる。
2. nは省略可能。(デフォルトは1)
3. 1～2000バイトまで格納できる。

• 10.15 NUMBER(p,s)について

1. pは精度。10進数桁の総数を表す。1～38桁。
2. sは位取り(小数点以下の桁数)。-84～127桁。
3. pは省略可能。その場合、浮動小数点を格納する。

• 10.15 DATEについて

1. 世紀、年、月、日、時間、分、秒を持つ。
2. 固定長でDBに格納される。
3. 日付書式のデフォルトは「DD-MON-RR」

【RR書式のデータ解釈】

1. RR =50～99で入力･･･1950～1999年(現在の年の上2桁-1の上2桁で戻される)
2. RR =0～49出入力････2000～2049年(現在の年と同じ上2桁で戻される)

• 10.18 TIMESTAMPについて(1)

TIMESTAMPデータ型はDATEデータ型を拡張したものである。
◆世紀、年、月、日、時間、分、秒、1秒未満の単位をもつ。

→時間を厳密に扱いたい場合に使用

【TIMESTAMP型の定義】[ ]内は省略可能

1. TIMESTAMP[(精度)]
2. TIMESTAMP[(精度)] WITH TIME ZONE
3. TIMESTAMP[(精度)] WITH LOCAL TIME ZONE

• 10.19 TIMESTAMPについて(2)

◆TIMESTAMP

1. ローカル時間(例えば日本標準時)のTIMESTAMP値
2. タイムゾーンによる時差･･･協定世界時(UTC)との時差(日本は+9時間)

◆TIMESTAMP WITH TIME ZONE

1. 主キーまたは一意キーの一部として指定できない。
2. 複数のタイムゾーンの時間の後先を厳格に評価する場合に使用する。

◆TIMESTAMP WITH LOCAL TIME ZONE

1. 主キーまたは一意キーの一部として指定できる。
2. 複数のタイムゾーンにわたって、クライアントのタイムゾーンで時間を指定する場合に適している。
3. 時差を列データとして含まない。

• 10.20 INTERVAL(期間データ型)について

【INTERVAL YEAR TO MONTH型の定義】[ ]内は省略可能
INTERVAL YEAR[(精度)] TO MONTH
※精度のデフォルトは2

【YEAR TO MONTH期間リテラルの定義】[ ]内は省略可能

1. INTERVAL '整数1-整数2' YEAR[(精度)] TO MONTH　･･･①
2. INTERVAL '整数1' YEAR[(精度)]･･･②
3. INTERVAL '整数1' MONTH[(精度)]･･･③

①INTERVAL '整数1-整数2' YEAR[(精度)] TO MONTH
◆整数1は年期間
◆整数2は月期間(値は0～11)
(例)現在時刻から1年6カ月後の日付を取得
SELECT SYSDATE,SYSDATE + INTERVAL '1-6' YEAR TO MONTH TARGET_DATE FROM dual;

SYSDATE ：2005/04/08 02:48:39
TARGET_DATE：2006/10/08 02:48:39

②INTERVAL '整数' YEAR[(精度)]
◆整数1は年期間

③INTERVAL '整数1' MONTH[(精度)]
◆整数1は月期間

• 10.22 LONG型について

LONG型は可変長文字列型である。

1. 最大2GBまでデータを格納できる。
2. 1つの表で1つの列にしか適用できない。
3. WHERE句、GROUP BY句、ORDER BY句の中では使えない。
4. LONG型の代わりに、新しいCLOB型があり、LONG型の制約のいくつかが改善されている。

• 10.24 制約の種類

1. NOT NULL制約･･･列にNULLを設定できないようにする。
2. 一意制約･･･指定された列(または列の組み合わせ)の値は、NULL値を除いて一意でなければならない。
3. 主キー制約･･･NOT NULL制約と一意制約の複合
4. 外部キー制約･･･値を使った関連付けを矛盾なく維持する。参照整合性制約または参照制約とも呼ばれる。
5. チェック制約･･･チェック条件を明示的に指定し、データをチェックする。

• 10.25 制約のガイドライン

1. 制約名を省略すると、OracleサーバーがSYS_Cn(nは一意な整数)の名前を生成する。
2. 制約名はオブジェクトのネーミング規則に従う。
3. 制約名は、1つのスキーマの中で一意である必要がある。
4. CREATE TABLE文で表を作成するときに制約を定義できる。
5. 表の作成後はALTER TABLE文で制約を追加できる。
6. 制約を定義する構文には、列レベルと表レベルの2つの構文がある。
7. データディクショナリービューで制約を確認できる。

(USER_CONSTRAINTS,USER_CONS_COLUMNS)

• 10.28 表レベル制約構文使用時の注意点

1. 他の列との組み合わせの制約(複合主キーなど)を指定できる。
2. NOT NULL制約は指定できない。
3. ALTER TABLEで制約だけを単独で追加する場合、表レベル構文を使う必要がある。

• 10.31 一意制約について

1. NULL値を除いて一意となる。
2. 重複値を入力するとエラーになる。
3. Oracleサーバーは暗黙的に一意索引を作成する。
4. 個数に制限はない。

【複合一意キーの定義】[ ]内は省略可能
･･･, [CONSTRAINT 制約名] UNIQUE(列名1,列名2),･･･

• 10.35 主キーについて

1. NULL値は禁止される。
2. 値は一意となる。
3. 1つの表には1つだけ主キーを定義できる。
4. Oracleサーバーは暗黙的に一意索引を作成する。

【複合主キーの定義】[ ]内は省略可能
･･･, [CONSTRAINT 制約名] PRIMARY KEY(列名1,列名2),･･･
★主キーを作成しなくてもエラーにはならないが、主キーがないと行の一意性が保証されないため、実用上は必須である。

• 10.39 外部キー制約

外部キー制約は、参照整合性制約または参照制約とも呼ばれる。値を使った関連付けを矛盾なく維持する制約。

1. 参照する列は外部キー、参照される列は親キーと呼ばれる。
2. 外部キーの値は(1)親キーの値のいずれかに一致 (2)NULL のどちらかでなければならない。
3. 外部キーが参照している親キーの値は削除できない。

→ON DELETE CASCADEオプションで削除可能。

外部キーを定義する前に、親キーには主キーまたは一意キー制約が定義されていなければならない。

• 10.41 外部キー制約設定時の親キー削除

外部キー制約が設定されている状態で親キーを削除する場合、次の2つのオプションが利用できる。

1. ON DELETE CASCADE･･･親キーを削除するときに、参照している外部キーの行も削除する。
2. ON DELETE SET NULL･･･親キーを削除するときに、参照している外部キーをNULLに置き換える。

• 10.42 チェック制約の定義

1. 列レベル構文では表内の他の列を参照できない。
2. 表レベル構文では表内の他の列を参照できる。
3. 表内の他の行の値を参照する問合せは使えない。
4. CURRVAL, NEXTVAL, LEVEL, ROWNUM擬似列は使えない。
5. SYSDATE, UID, USER, USERENV関数は使えない。

• 10.46 副問合せを使った表作成

【副問合せを使った表作成の基本構文】[ ]内は省略可能
CREATE TABLE 表名[(列名, 列名･･･)] AS 副問合せ;

【副問合せを使った表作成のポイント】

1. 指定する列数と副問合せの列数が一致する必要がある。
2. 列名、デフォルト値、制約を指定できる。
3. SELECT句で式を指定した場合は、列名または列別名を指定しなければならない。
4. データ型、NOT NULL制約、データが表にコピーされる。また、列を指定しない場合、列名がコピーされる。

★DEFAULT値や、NOT NULL以外の制約はコピーされない。

• 10.50 DROP文による表の削除

1. 表内の構造が全て削除される。(データや制約など)
2. 表に作成された索引は自動的に削除される。
3. DDL文なのでトランザクションはコミットされる。

表が使用していた全ての領域は解放され、ほかのオブジェクトで再利用可能となる。

1. 表の所有者またはDROP ANY TABLEシステム権限を持つユーザーだけが削除できる。
2. 表に依存するオブジェクトは削除されないが無効になる。(ビュー、シノニムなど)