第11章 DDL文を使用した表の作成と管理

11.1 表の作成と削除
スキーマとは、データベースオブジェクトを管理するために使用される、論理的な概念である。
Oracleサーバの各ユーザは、ユーザ名と同じ名前のスキーマを1つ所有している。
各ユーザが作成したオブジェクトは、そのユーザが所有するスキーマに格納される。

スキーマは、ユーザが所有するオブジェクトのリストである。
スキーマは、あくまでも論理的な概念であるため、実際に領域が割り当てられるわけではない。

■別のユーザが所有するオブジェクトを参照する方法
あるユーザが、自身が所有するスキーマ以外のスキーマ内のオブジェクトを参照する場合は、
オブジェクト名に接頭辞(スキーマ名)を付けて、スキーマ名.オブジェクト名の形式で指定する必要がある。

▼別のユーザが所有するオブジェクトの参照
スキーマ名.オブジェクト名

例)ORA02ユーザが、ORA01ユーザの所有するスキーマ内のEMP表を参照する場合は、
ora01.EMP
と指定する必要がある。

ユーザは、ユーザ名と同じ名前のスキーマを1つ所有する。
他のユーザが所有するスキーマ内のオブジェクトを参照するには、スキーマ名.オブジェクト名を指定する必要がある。

■オブジェクトの命名規則
・長さは、30バイト以下(文字数ではなく、バイト数)
・先頭の文字は、数字以外の文字(数字および記号は不可)
・使用できる文字は、英数字(A~Z、a~z、0~9)および、漢字、カタカナ、ひらがな(日本語環境の場合)
・使用できる記号は、_アンダーバー、$、#の3種類のみ。
・同一のスキーマ内で重複する名前は、指定できない。
・Oracleサーバの予約語(SELECT、FROM、ORDERなど)は使用できない。
・アルファベットの大文字・小文字は区別されない。

■表の作成
CREATE TABLE 表名
(列名 データ型
);

■DEFAULTオプション
列の定義にDEFAULTオプションを指定すると、その列にデフォルト値を設定できる。
列名 データ型 [DEFAULT 式]

デフォルト値には、リテラル値、式または、SQL関数(SYSDATEやUSERなど)を指定できる。
NEXTVAL疑似列
CURRVAL疑似列
別の列の名前は指定できない。

▼重要
表を作成するには、CREATE TABLE権限が必要である。
DEFAULTオプションを指定することで、任意の列にデフォルト値を設定できる。
DEFAULT値には、リテラル、式、SQL関数(SYSDATE、USERなど)を指定できる。
DEFAULT値には、別の列の名前は、指定できない。

■表の削除
表を削除するには、DROP TABLE文を使用する。
DROP TABLE文の基本構文は、
DROP TABLE 表名[PURGE];
表の所有者または、DROP ANY TABLE権限を持つユーザのみ、表を削除できる。

表を削除すると、表内のすべてのデータに加えて、表に定義されている制約や索引も削除される。
一方で、表を参照するビューやシノニムは削除されず、無効になるだけ。
DROP TABLE文を実行すると、表は、ゴミ箱に移動して、完全に削除されるわけではない。表領域は解放されないので、再利用はできない。
表のすべてのデータと表の構造が削除される。
表を完全に削除するには、PURGE句を指定する。

表の完全削除
DROP TABLE EMP1 PURGE;
削除された表をゴミ箱から復元するには、FLASHBACK TABLE文を使用する。

■11.2 データ型

■データ型とは、
データ型とは、Oracleサーバで扱うデータの形式のこと。
データ型は表の作成時に列ごとに定義する。
列には定義されたデータ型のデータしか格納できない。

■文字型
VARCHAR2型
CHAR型
LONG型
CLOB型
NCLOB型

▼文字型の定義方法
列名 VARCHAR2(最大サイズ)
列名 CHAR[(最大サイズ)]
列名 LONG
列名 CLOB
列名 NCLOB

最大サイズ 4000バイト 2000バイト

■LONG型の制限
LONG型は、VARCHAR2型やCHAR型の列には格納できない。
大きな文字データを格納できるデータ型のため、LONG型を定義した列では、制限がある。
・副問合せを使用した表の作成時に、LONG列はコピーできない(エラーが発生する)
・LONG列は、GROUP BY句とODER BY句に指定できない。
・LONG列は、1つの表に1つだけ定義できる。(LONG列または、LONG RAW列のどちらか1つだけ)
・LONG列には、制約を定義できない。

■数値型
列に数値データを格納するには、NUMBER型を用いる。

最大精度と位取りの両方を省略すると、最大38桁の有働小数点を格納できる。

例)NUMBER(5,2)を定義した場合、
123.456の値をセットすると、5桁で小数点第2位までの値となり、それ以下を四捨五入する。ので、123.46が格納される。

■日付型

DATE型は、サイズ指定できない。

■バイナリデータ型

データ型 説明
RAW型 最大2000バイトまでのバイナリデータを格納できる。可変長データ型。最大サイズの指定は省略できない。
LONG RAW型 最大2GBまでのバイナリデータを格納できる。可変長データ型。LONG型と同様の制限がある。
BLOB型 最大4GBまでのバイナリデータを格納できるデータ型。
BFILE型 最大4GBまでのバイナリデータを格納できる、読み取り専用データ型。BFILE型が定義された列に格納した
データは、Oracleサーバのデータファイル上ではなく、OS上のファイルに保存される。

■ROWID型
ROWIDは、表の各行に割り当てられている一意のアドレス(BASE64文字列)である。

■その他のデータ型

TIMESTAMP型
INTERVAL YEAR TO MONTH型 2つの日付・時刻の間隔を年、および月の単位で格納する。
INTERVAL DAY TO SECOND型 2つの日付・時刻の間隔を日、時、分、秒の単位で格納する。

TIMESTAMP型 DATE型を拡張したデータ型である。DATE方の列が格納できる。月日時分秒、秒の小数点以下も格納する。

TIMESTAMP WITH TIME ZONE タイムゾーンの時差を含む。タイムゾーンの時差は、列の一部として格納・表示される。
TIMESTAMP WITH LOCAL TIMEZONE タイムゾーンの時差を含む。列の一部として格納せず、
データ取得時にローカルセッションのタイムゾーンの値で表示される。

■11.3 制約の種類と指定方法

■制約とは、
制約とは、表に格納するデータに関するルール。

制約のチェックは、データの変更時(追加、更新、削除)に自動的に行われる。
▼制約の種類
NOT NULL制約 NULL値を許可しない。
UNIQUE制約(一意制約) 重複値を許可しない。複数のNULL値は、許可する。
PRIMARY KEY制約(主キー制約) 表内の各行を一意に識別できる値のみ許可する。重複値およびNULL値は、許可しない。
FOREIGN KEY 参照先の列にある値または、NULL値のみ許可する。
(外部キー制約・参照整合性制約) 列レベルで記述する時は、FOREIGN KEYキーワードは使わない。
CHCK制約(チェック制約) 指定した条件に合う値のみ許可する。

■制約の定義方法
制約は、列レベルまたは、表レベルで定義できる。
列レベルで定義した制約を列レベル制約、表レベルで定義した制約を表レベル制約と呼ぶ。
作成された制約の機能は同じである。

制約を定義する際の注意点
・CONSTRAINT 制約名は、省略可能。省略時は、OracleサーバからSYS_Cnの形式で名前を作成する。
・表レベル制約の構文では、()丸括弧内に制約を定義する列を1つ以上指定する。
・1つのCREATE TABLE文の中に列レベル制約と表レベル制約を同時に指定できる。
・NOT NULL制約は、列レベルでのみ定義できる。
・複数の列の組み合わせからなる場合の制約は、表レベルでのみ定義できる。
・1つの列に複数の列レベル制約を定義する場合は、改行またはスペースで区切る(カンマではない)
・1つの表に複数の表レベル制約を定義する場合は、,カンマで区切る(改行やスペースではない)

■表作成時の制約の定義構文

■列レベル制約の基本構文
CONSTRAINT 制約名 制約の種類

制約を定義する際の注意点
CONSTRAINT 制約名は、省略可能(省略時は、OracleサーバがSYS_Cnの形式で名前を作成する)
表レベル制約の構文では、()丸括弧内に制約を定義する列を1つ以上指定する。
1つのCREATE TABLE文の中に列レベル制約と表レベル制約を同時に指定できる。
NOT NULL制約は、列レベルでのみ定義可能。
複数の列の組合せからなる場合の制約は、表レベルでのみ定義できる。
1つの列に複数の列レベル制約を定義する場合は、改行またはスペースで区切る(カンマではない)
1つの表に複数の表レベル制約を定義する場合は、カンマで区切る(改行やスペースではない)

■PRIMARY KEY 制約(主キー制約)

PRIMARY KEY制約を定義すると、その列には、表内の各行を一意に識別できる値しか格納できなくなる。
重複値やNULL値を格納することはできない。

NOT NULL PRIMARY KEY という2つの制約を定義できると問題集の第11章26は、解説しているがにわかには信じがたい。
例)
▼列レベルの制約定義
CREATE TABLE EMP
(ENAME VARCHAR2(10) NOT NULL PRIMARY KEY);

▼表レベルの制約定義のため、エラー
CREATE TABLE EMP
(ENAME VARCHAR2(10) PRIMARY KEY, NOT NULL(ENAME));

FOREIGN KEY制約(外部キー制約)を定義すると、その列には、参照先の列に存在する値しか格納できない。
FOREIGN KEY制約(外部キー制約)を定義しても、NULL値を含めることができる。
FOREIGN KEY制約は、UNIQUE制約または、PRIMARY KEY制約が定義されている列しか参照できない。
FOREIGN KEY制約を定義すると、親表は削除できない。

■CHECK制約
条件に対して、TRUEまたは、NULLを戻す値しか格納できない。

▼許可されていない事項
CURRVAL、NEXTVAL、LEVEL、ROUNUM疑似列の参照
SYSDATE、UID、USER、USERENV関数の呼び出し
他の行の値を参照する問合せ

■列の削除
ALTER TABLE 表名 DROP (列名 [, 列名]);

▼列を削除する際の注意点
削除の対象の列には、データが存在する場合も存在しない場合もある。
列を削除した後も、表には1つ以上の列を残す必要がある。
列の削除は、元に戻せない。
別の列から参照される主キーは、CASCADEオプションを指定しない限り、削除できない。
列に多くの値が含まれている場合は、削除に時間がかかる。

1度に1つの列のみを削除する場合は、
ALTER TABLE DROP COLUMN文を使用できる。

UNUSEDマークが設定されている列は、削除された列と同等の扱いになるため、同じ名前の列を作成できる。

ALTER TABLE 表名
SET UNUSED(列名);

ALTER TABLE 表名
SET UNUSED COLUMN(列名);

ALTER TABLE 表名 DROP UNUSED COLUMNS;

ALTER TBALE SET UNUSED
UNUSEDマークが設定されている列は、削除された列と同等の扱いになるため、同じ名前の列を作成できる。
UNUSEDマークが設定されている列の名前の確認や、取消は行えない。
列の列数だけは、USER_UNUSED_COL_TABSデータディクショナリで確認ができる。
UNUSEDマークが付けた列に索引がある場合は、索引は削除され、制約も削除される。

通常の列はUNUSEDマークを付けると、すぐに同じ名前の列を追加できる。
LONG型の列は、例外で、未使用のLONG列を物理的に削除しない限り、その表に別のLONG列を追加することができない。
ビューは自動的に修正されない。

表を作成するために必要なものは2つ
1.CREATE TABLEシステム権限
   また、CREATE ANY TABLEシステム権限を持っていれば、他のスキーマに表(他ユーザが所有する表)を作成できる。
2.記憶域 表を格納する記憶域が必要。

順番とは、一意な番号を生成するオブジェクトのこと。

シノニムは、オブジェクトに対する別名のことで、CREATE SYNONYM文で別名を定義できる。
CREATE TABLE文では、スキーマ、別のデータ型および制約などを指定する。

スキーマとは、オブジェクトの所有者を表す概念である。具体的にはユーザが所有するオブジェクトの集合(リスト)を表す。
データベースユーザには、必ず1つのスキーマを持ち、スキーマ名には、ユーザ名が使われる。

SQL DeveloperおよびSQL * Plusにおいて、DESCRIBEコマンド(短縮形:DESCコマンド)を使って、表の構造を参照できる。
LISTコマンド、SHOWコマンドは、SQL * Plusのコマンドである。
LISTコマンド(短縮形:Lコマンド)は、SQLバッファ内のSQL文を表示する。
SHOWコマンド(短縮形:SHOコマンド)は、SQL * Plusの実行環境(システム変数)を表示する。

VARCHAR2(n)のnは、最大長を表し、nを超えるデータはエラーと成る。

CHAR型
CHAR(n)
nを超えるデータはエラーとなり、nに満たないデータは、後ろに空白を追加して、nまで拡張される。
nは省略可能である。デフォルトは1

NUMBER型
NUMBER(p,s) pは10進数桁の総数を表し、範囲は1~38桁になる。
sは、位取り(小数点以下の桁数)であり、sの範囲は、-84~127桁である。

NUMBER(p,s)で、値が精度の有効範囲を超える(整数部の桁が不足する)とエラーとなる。
値が位取りの有効範囲を超える(小数部の桁が不足する)と丸められる。

DATE型
データベースには、固定長で格納される。
デフォルトは、 DD-MON-RR
DD-MON-YY 2005年に01-JAN-99と入力すると01-JAN-2099と解釈される。

ある日付から別の日付を引くと日数を表す数値が戻る。日付ではなく日数を表す数値となる。

▼制約に関する
表に無効なデータが入力されないようにする。
値を使った関連付けを矛盾がないように維持する。(表の削除や更新を防止)
アプリケーションごとに制約を記述する必要がないため、生産性が向上する。
制約は、チェックのために時間が余計にかかり、パフォーマンスを低下させる。

▼列レベル使用時の注意点
列定義の中で制約を指定し、その列に対する制約のみを定義する。他の列と組み合わせた制約(複合主キーなど)は指定できない。
NOT NULL制約は、列レベル構文でのみ指定できる。他の制約は、表レベルおよび列レベルのどちらでも指定できる。

▼表レベル使用時の注意点
列定義とは別に、制約だけを単独で指定する。制約の定義中に列を含む。他の列との組み合わせの制約(複合主キーなど)を指定できる。
NOT NULL制約は、指定できない。
ALTER TABLE文で制約だけを単独で追加する場合は、表レベル構文を使う必要がある。

Oracleサーバは、暗黙的に一意な索引を作成し、UNIQUE制約を適用する。
複合一意キー(列の組合せで一意である)は、表レベルの構文で指定する。
CONSTRAINT 制約名 UNIQUE(列名1,列名2)

複合一意キーは、組合せた列でNULL値を除き、一意である必要がある。

表一つに対して、主キーは、必ず一つ設定する必要はない。

PRIMARY KEY制約では、複合キーを指定できるのは、表レベル構文だけである。
列レベル
CREATE TABLE EMP (EMPNO NUMBER(6) PRIMARY KEY);

表レベル
CREATE TABLE EMP(EMPNO NUMBER(6),
CONSTRAINT EMP_ENAME_UQ PRIMARY KEY(EMPNO));

FOREIGN KEY制約は、参照整合性制約または、参照制約とも呼ばれている。

参照する列は、外部キー、参照される列は、親キーと呼ぶ。
外部キーの値はNULLであるか、あるいは親キーの値のいずれかに一致しなければならない。
外部キーが参照している親キーの値は削除できない。(ただし、ON DELETE CASCADEオプションで削除可能)
外部キーを定義する前に、親キーにはPRIMARY KEY制約または、UNIQUE制約が定義されていなければならない。
自表は、親表にできない。
FOREIGN KEY制約を設定した後、親表の主キーを変更できない。

■副問合せを使い、既存の表から新しい表を作成できる。
CREATE TABLE 表名 [列名1,列名2,・・・]
AS 副問合せ;

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

データ型とNOT NULL制約は、新しい表にコピーされる。
NOT NULL制約以外の制約はコピーされない。
副問合せのSELECT句で計算式や関数を使用している場合は、明示的に列名(列別名)を指定する必要がある。

■ALTER TABLE
ALTER TABLE文では、列の追加、定義の変更、列の削除、列の名前の変更、読み取り専用状態への表の変更が行える。
列の追加、列定義の変更時に列のでDefault値の追加、変更もできる。
表の切捨てには、TRUNCATE文を用いる。

ALTER TABLE文を使い、表を読取り専用状態に変更できる。
ALER TABLE 表名 READ ONLY | READ WRITH;

読取り専用状態の表に対して、INSERT文、DELETE文、UPDATE文、SELECT ~ FOR UPDATE文を発行することはできない。
表内のデータを変更するDDLBん(TRANCATE文)なども発行できない。
ただし、DROP TABLE文を発行して、表を削除することは可能である。

▼DROP TABLE文のガイドライン
表および表に作成された索引、制約は削除され、ゴミ箱に移動されて使用できなくなるが、
FLASHBACK TABLE文を使って、元の使用可能な状態にリストアできる。間違って表を削除した場合に、簡単に回復できる。

DDL文のため、トランザクションは、コミットされ、ロールバックできなくなる。
PURGE句を指定した場合は、表および、表に作成された索引はゴミ箱に移動されず、完全に削除される。
その結果、表および索引が使用していな領域は解放され、ほかのオブジェクトで再利用が可能となる。
表の所有者または、DROP ANY TABLEシステム件G年を持つユーザだけが表を削除できる。
表に依存するオブジェクト(ビュー、シノニムなど)は削除されないが無効となる。

アルファベットを含む部門コード3桁の適切なデータ型は、CHAR(3)である。固定長のため、CHAR型を使った方が、必要以上のハードディスク洋々を使わずに済む。
画像データなどのバイナリデータを格納するのは、BFILE型である。
可変長バイナリデータを格納するのは、BLOB型である。

あわせて読みたい