11. 表の作成と削除

11.1 表の作成と削除

データベースオブジェクト…表、ビュー、索引などのデータ構造を格納できるものの総称。単にオブジェクトととも。

スキーマ

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

スキーマは「ユーザーが所有するオブジェクトのリスト」である。

ただし、あくまでもスキーマは論理的な概念であり、実際に領域が割り当てられるわけではない。

別のユーザーが所有するオブジェクトを参照する方法

自身が所有するスキーマ以外のスキーマ内のオブジェクトを参照する場合、オブジェクト名に接頭辞（スキーマ名）をつけて指定する

スキーマ名.オブジェクト名

オブジェクトの命名規則

・長さ30バイト以下

・先頭の文字は数字、記号以外の文字

・使用できる文字は英数字、漢字、カタカナ、ひらがな（日本語環境のばあい）

・使用できる記号は、「_」「$」「#」のみ

・同一スキーマ内で重複する名前は指定できない

・Oracleサーバの予約語は使用できない

・アルファベットの大文字小文字は区別されない

表の作成

CREATE TABLEで作成するが、作成するためにはCREATE TABLE権限が必要。

権限の付与はデータベースの管理者が、データ制御言語を使用して実行する。

基本構文

CREATE TABLE [スキーマ名].表名

(

列名 データ型

[,列名 データ型 …］

);

スキーマ名を省略すると、CREATE TABLEを実行したユーザーが所有するスキーマ内に表が作成される。

DEFAULTオプション

表の作成時にDEFAULTオプションを指定すると、その列にデフォルト値（データの追加時にその列に対する値を省略した場合にその列に格納される値）を設定できる。

列名 データ型 [DEFAULT 式]

式が戻す値はデータ型と一致していなくてはならない。

リテラル値、式、sql関数を指定できる。

順序オブジェクトを参照するNEXTVAL擬似列やCURRVAL擬似列名も指定できる。（12cから）

別の列の名前は指定出来ない。

表の削除

DROP TABLE 表名 [PURGE];

表を削除できるのは、表の所有者またはDROP ANY TABLE権限を持つユーザーのみ。

表を削除すると表内のすべてのデータ、表に定義されている制約や索引も削除される。

表を参照するビューやシノニムは削除されない。

表はゴミ箱に移動される。

PURGE句を使用すると完全に削除される。 

11.2  データ型

文字型：列に文字データを格納できるようにするために使用する

>CHAR,VARCHAR2,LONG,CLOB,NCLOB

◆CHARとVARCHAR2の違い

CHAR

最大サイズ：2000バイト

最大サイズ指定省略時：デフォルト値の１が最大サイズ指定となる

確保するデータサイズ：固定長。格納するデータのサイズに関わらず、表作成時に定義したサイズで一定

VARCHAR2

最大サイズ：4000バイト

最大サイズ指定省略時：不可（省略するとエラー）

確保するデータサイズ：可変長。格納するデータサイズに応じて変わる。

◆LONG型

CHARやVARCHAR2に格納できない大きな文字データを格納できる(最大2GB)

・副問合せを使用した表の作成時に、LONG列はコピーできない。

・GROUP BY句、ORDER BY句に指定できない。

・1つの表に１つだけ定義できる。（LONG列またはLONG RAW列のどちらか１つ）

・制約を定義できない。

数値型：列に数値にを格納できるようにするために使用する

>NUMBER

定義方法

列名 NUMBER [ ( 最大精度 [,位取り ] ) ]

NUMBER型の構文のオプション

最大精度：格納する数値データの最大精度を指定する。

位取り：格納する数値データの小数点以下の桁数を指定する。

最大精度と位取りの両方を省略すると最大38桁の浮動小数点を格納できる。最大精度を指定して、位取りを省略した場合あ、最大精度に指定した桁数の整数値を格納できる。

日付型：列に日付データを格納できるようにするために使用する

>DATE

データサイズは固定長で7バイト。

世紀、年、月、日、時、分、秒が内部的な数値書式の形式で格納される。

バイナリ型：列にバイナリデータを格納できるように使用する

>RAW,LONG RAW,BLOB BFILE

RAW：最大2000バイトまでのバイナリデータを格納できる。可変長、最大サイズの指定はできない。

LONG RAW：最大2GBまでのバイナリデータを格納できる、可変長、LONG型と同様の制限あり。

BLOB：最大4GBまでのバイナリデータを格納できる

BFILE：最大4GBまでのバイナリデータを格納できる、読み取り専用。BFILE型が定義された列に格納したデータは、Oracleサーバのデータファイル上ではなく、OS上のファイルに保存される。

ROWID型

>ROWID

 ROWID疑似列(実際には定義されていないが、表に定義された列のように使用できる疑似的な列)から戻される値を列に保存する場合に使用する。ROWIDは表の各行に割り当てられている一意なアドレスのこと。

TIMESTAMP型

DATE型を拡張したもの。DATE型が格納できる情報に加えて、秒の小数点以下の値も格納できる。小数点以下の桁数には0～9の範囲が指定できる（デフォルトは６） 

>TIMESTAMP WITH TIME ZONE

タイムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納され、表示される。

>TIMESTAMP WITH LOCAL TIMEZONE

タイムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納されず、データ取得時にローカルセッションのタイムゾーンの値で表示される。

 11.3 制約の種類と制定方法

制約とは 表に格納するデータに関する「ルール」

制約を定義することで、ルールに反する追加や、ルールに満たさなくなるようなデータの更新・削除を行えなくなる。

制約のチェックは、データの変更時（追加、更新、削除）に自動的に行われる。

NOT NULL制約：NULL値を許可しない。

UNIQUE制約(一意制約)：重複値を許可しない。複数のNULL値は許可する。

PRIMARY KEY制約(主キー制約)：表内の各行を一意に識別できる値のみ許可する。重複値およびNULL値は許可しない。

CHECK制約(チェック制約)：指定した条件に合う値のみ許可する。

制約の定義方法

 制約は列レベルまたは表レベルで定義する。

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

CREATE TABLE [スキーマ名].表名

(

列名  データ型  [ [列レベル制約 [ 列レベル制約…]

[,列名  データ型 [列レベル制約 [ 列レベル制約…] ]…]

[,表レベル制約 [ , 表レベル制約 ] ]

); 

 列レベル制約の基本構文

[CONSTRAINT 制約名 ] 制約の種類

表レベル制約の基本構文

[CONSTRAINT 制約名] 制約の種類 ( 列名 [, 列名…] )

・「CONSTRAINT制約名」は省略可能。省略時はOracleサーバが「SYS_Cｎ」の形式で名前を作成する

・表レベル制約の構文では、「()」内に制約を定義する列を1つ以上指定する。

・１つのCREATE TABLE文の中に列レベル制約と表レベル制約を同時に指定できる。

・NOT NULL制約は列レベルでのみ定義する

・複数の列の組み合わせからなる複合の制約は表レベルでのみ定義できる。

・1つの列に複数の列レベル制約を定義する場合は、改行またはスペースで区切る（カンマではない）

・１つの表に複数の表レベル制約を定義する場合は、カンマで区切る（改行、スペースではない）

NOT NULL制約

その列にNULL値を設定できなくなる。

NOT NULL制約は列レベルでのみ定義できる。

UNIQUE制約（一意キー制約）

その列に重複した値を設定できなくなる。

NULL値を含めることはできる。（複数行もできる）

自動的に制約と同じ名前の一意索引が作成される。

PRIMARY KEY制約（主キー制約）

その列（または組み合わせ）には表内の各行を一意に識別できる値しか格納できなくなる。

重複値、NULL値は設定できない。

表に1つのみ設定できる。

自動的に制約と同じ名前の一意索引が作成される。

FOREIGN KEY制約（外部キー制約)

 その列には参照先の列に存在する値しか格納できなくなる。

NULL値を含めることができる。（複数行もできる）

列レベルでの構文

[ CONSTRAINT 制約名] REFERENCES 親表名(参照する列名 [, 参照する列名…])

表レベルでの構文

[ CONSTRAINT 制約名] FOREIGN KEY ( 列名 [, 列名…])

REFERENCES 親表名 ( 参照する列名 [,参照する列名…])

親表名

参照先の表の名前。FOREIGN KEY制約で参照する表を「親表」、FOREIGN KEY制約が定義されている表を子表と呼ぶ。親表には別の表だけではなく、FOREIGN KEY制約を定義する表(つまり同じ表）を指定することもできる。

参照する列名

親表に定義されている、参照先の列の名前。

ただし、FOREIGN KEY制約はUNIQUE制約またはPRIMARY KEY制約が定義されている列しか参照できない。

ON DELETE CASCADE

子表に親表を参照する行が存在する場合に，親表の参照されていない行を削除すると，子表の参照している行も削除される

ON DELETE SET NULL

子表に親表を参照する行が存在する場合に，親表の参照されている行を削除すると，子表の参照している行にNULL値が設定される

 CHECK制約

その列には指定した条件に対してTRUEまたはNULLを戻す値しか格納できなくなる。

CHECK制約は1つの列に複数定義できる。

構文

[ CONSTRAINT 制約名 ] CHECK(条件)

・CURRVAL,NEXTVAL,LEVEL,ROWNUM疑似列の参照はできない

・SYSDATE、UID,USER,USERENV関数の呼び出しはできない

・他の行の値を参照する問い合わせはできない

11.4 副問合せを使用した表の作成と表構造の変更

副問合せを使用した表の作成

CREATE TABLEと副問合せを組み合わせると、既存の表を基にして新しい表を作成できる。

CREATE TABLE 表名 [ ( 列名 [,列名…] ) ]

AS

副問合せ;

・データ型とNOT NULL制約は新しい表にコピーされる

・NOT NULL制約以外の制約はコピーされない

・副問合せのSELECT句で計算式や関数を使用している場合，明示的に列名（列別名）を指定する必要がある。

表構造の変更

表の構造はあとから変更できる。

ALTER TABLE句を使用すると以下の操作が可能

・表に新しい列を追加する

・既存の列のデータ型を変更する

・既存の列にデフォルト値を設定する

・既存の列を削除する

・既存の列の名前を変更する

・表を読み取り／書き取りモードにする

・表を読み取り専用モードにする

◇列の追加

 ALTER TABLE　表名

ADD( 列名　データ型　[ DEFAULT　式] [ 列レベル制約 ]

　　[, 列名　データ型　[ DEFAULT 式] [ 列レベル制約 ]　… ] ) ;

 新しく追加された列にはNULL値が入っているためNOT NULL制約をつけたい場合はDEFAULT値を設定する必要がある。

◇列の変更

既存の列のデータ型，サイズ，デフォルト値を変更できる

ALTER TABLE 表名

MODIFY(列名 [ データ型 ] [ DEFAULT式 ]

　　　　　　[,列名　[ データ型 ] [ DEFAULT　式 ]… ]);

 ・サイズ，精度の増加はいつでもできる

・列のサイズは次の場合のみ減少できる

→列にNULL値のみが含まれている

→表に行がない

→列のサイズは，既存の列の値未満には減少できない

（すでに30バイトのデータが入ってる列のサイズを20にはできない）

・列にNULL値のみが含まれている場合は，データ型を変更できる

・列にNULL値以外のデータが含まれていてもサイズを変更しない場合は，CHAR型からVARCHAR2型，またはその逆へはデータ型を変更できる

・列のデフォルト値の変更は，以後の表への挿入のみに適用される。

◇列の削除

ALTER TABLE　表名　DROP(列名 [,列名…]);

・削除の対象の列にはデータが存在する場合も，存在しない場合もある

・列を削除した後も，表には1つ以上の列を残す必要がある

・列の削除は元に戻せない。

・別の列から参照される主キーは，CASCADEオプションを指定しない限り削除できない

・列に多くの値が含まれている場合は削除に時間がかかる

◇表モードの変更

「読み取り／書き込みモード」と「読み取り専用モード」がある

 表のメンテナンスを行う際に，読み取り専用モードにするとDDLやDMLによる変更を防ぐことができる。

ALTER TABLE　表名{ READ WRITE | READ ONLY };

読み取り専用モードにしても，表の削除はできるので注意。