DDLを使ったオブジェクトの定義

業務では、何らかのアプリケーションを使ってデータベースのデータを管理や運用しますが、そのデータを格納するためのテーブルを作成する必要があります。

ここでは、テーブルの管理をする際に登場するDDL(データ定義言語)について記載します。

1. データベースオブジェクト


データベース内で使用される表やビュー、索引などを総称したものになります。単にオブジェクトと呼ばれる場合もあります。

オブジェクトには、特定のスキーマに紐づいたオブジェクトと紐づかないオブジェクトが存在します。

1-1. 特定のスキーマに紐づいたオブジェクト

特定のスキーマに紐づいたオブジェクトのことをスキーマオブジェクトと呼びます。

特定のユーザーがオブジェクトを所有します。オブジェクトは、所有するユーザーのスキーマに格納されます。

主にテーブルや索引、ビューなどのオブジェクトが対象になります。

1-2. 特定のスキーマに紐づかないオブジェクト

特定のスキーマに紐づかないオブジェクトのことを非スキーマオブジェクトと呼びます。

ユーザーがオブジェクトを所有せず、スキーマにも格納されないオブジェクトになります。


主にユーザー、ロール、表領域(※1)などが対象になります。

※1 表領域は、データを格納する場所

2. DDL(データ定義言語)


テーブルやビュー、索引などのオブジェクトを定義する際に使用します。

主にテーブルを新規作成(CREATE)、テーブルの定義変更(ALTER)、テーブルの削除(DROP)などが該当します。

2-1. オブジェクト名とカラム名の命名規則

オブジェクト名とカラム名を定義する際の主な命名規則は、次の通りになっています。


・一般的には先頭文字はアルファベットである必要があります。ただし、「"(ダブルクオーテーション)」で囲むことで、先頭にアルファベット以外の文字が使用できます。

・予約語は使えません。

・大文字と小文字は区別されませんが、「"(ダブルクオーテーション)」で囲むことで大文字と小文字が区別されます。

・ユーザーが所有するオブジェクト名には同じ名前は使用できません。ただし、ユーザーが異なる場合は使用できます。(※1)

・テーブル内に同じカラム名を使用することはできません。

・英数字、「$」「_」「#」以外の文字を使用する場合は、「"(ダブルクオーテーション)」で囲む必要があります。日本語を使用する場合などは「"(ダブルクオーテーション)」で囲んで使用します。


※1 以下のオブジェクトは、オブジェクトの種類が異なるため、同じ名前が使用できます。

・索引

・制約

・トリガー

3. カラムのデフォルト値設定


CREATE TABLE文を実行する際にカラムに対してデフォルト値を設定できます。

デフォルト値が設定されていないカラムに空の値を追加するとNULLが設定されます。

3-1. デフォルト値を指定

カラムにデフォルト値を指定する構文は、次の通りになります。

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> DEFAULT <デフォルト値>
    [, <カラム名2> <データ型> DEFAULT <デフォルト値>] );

・デフォルト値を設定するには、データ型の後にDEFAULTオプションを指定します。

・デフォルト値には、リテラル、式、ファンクションを指定できます。

・デフォルト値のデータ型は、カラムのデータ型と一致させる必要があります。

◆デフォルト値を指定した場合の動作イメージ

例として、B列には、デフォルト値として「NoName」を指定、C列にはデフォルト値の指定なしのテーブルにデータを追加します。

B列には、デフォルト値が設定されているため「NoName」が格納されており、C列にはデフォルト値を設定していないため「NULL」が格納されています。

4. 制約とキー


データベースに格納されたデータの整合性を保つために制約を設けて整合性を維持しています。

4-1. 制約とは

データベースに格納されたデータは、様々な用途で使用されますが不適切なデータが格納されないよう考慮する必要があります。

その際に、制約(整合性制約)を設けてデータベースに不正なデータが格納されるのを防ぎデータの整合性を維持しています。

制約には、次の種類があります。


・NOT NULL制約

・チェック制約(CHECK制約)

・一意制約(UNIQUE制約)

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

・外部キー制約(FOREIGN KEY制約)

4-2. 制約の指定方法

制約の指定方法には、「表内指定(列制約)」と「表外指定(表制約)」の2つの方法があります。

表内指定(列制約)は、カラムに対して制約を指定します。

表外指定(表制約)は、カラムまたは複数カラム、テーブル全体に対して制約を指定します。


◆表内指定(列制約)

表内指定(列制約)を使った制約を行うには、テーブル作成時にカラムのデータ型の後に制約を指定します。構文は、次の通りになります。

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> <制約1>
    [, <カラム名2> <データ型> <制約2>] );

◆表外指定(表制約)

表外指定(表制約)を使った制約を行うには、テーブル作成時に全てのカラムを指定した後に「,(カンマ)」で区切って制約を指定します。

構文は、次の通りになります。

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
    , <カラム名1> <データ型>
    , <制約1>
, <制約2>);

◆制約に名前を付ける

作成する制約に名前を付ける場合は、制約の前にCONSTRAINT句を指定します。

制約に名前を付ける構文は、次の通りになります。

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> CONSTRAINT <制約名> <制約1>
    [, <カラム名2> <データ型> <制約2>] );

・表内指定(列制約)、表外指定(表制約)どちらも制約の前にCONSTRAINT句を指定します。

・CONSTRAINT句は省略可能です。省略した場合、「SYS_C<数字>」の制約名が自動的に割り当てられます。

◆制約を指定する際の注意事項

・NOT NULL制約は、表内指定(列制約)で制約を指定する必要があります。表外指定(表制約)では、NOT NULL制約を指定できません。

・NOT NULL制約以外は、表内指定(列制約)と表外指定(表制約)のどちらでも制約を指定できます。

・複数のカラムに対して制約を指定する場合は、表外指定(表制約)で指定する必要があります。

4-3. NOT NULL制約

制約を適用したカラムで必ず値を設定することを強制する制約です。

値を設定しない場合は、制約に違反することになりエラーとなります。


これにより、適用したカラムでNULLが設定されるのを防ぐことができます。


NOT NULL制約を指定する構文は、次の通りになります。

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> [CONSTRAINT <制約名>] NOT NULL
    [, <カラム名2> <データ型> <制約2>] );

4-4. 主キー制約(PRIMARY KEY制約)

テーブルに格納されたレコードからそれぞれを識別する役割を持ったカラムのことを「キー」と呼んでいます。

主キー制約は、主キー(テーブルから1件のレコードを特定できるカラム)を実現するためのルールとなります。


主キー制約が適切に動作するためには、主キーとなるカラムで重複した値が登録されないようにする必要があります。

重複したデータが登録された場合、主キーのカラムから1件のレコードを特定することができなくなります。

主キー制約を指定したカラムは、値を設定することが強制されるため、NULLの入力が禁止されます。(NOT NULL制約と同じ)


また、複数のカラムを組み合わせて主キーとすることもできます。

このことを複合主キーと呼んでいます。複合主キーを行うには、表外指定(表制約)で指定する必要があります。


主キー制約を指定する構文は、次の通りになります。

・表内指定(列制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> [CONSTRAINT <制約名>] PRIMARY KEY
    [, <カラム名2> <データ型> <制約2>] );

・表外指定(表制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
    , <カラム名1> <データ型>
    , [CONSTRAINT <制約名>] PRIMARY KEY (カラム名1 [, カラム名2] ) );

・主キー制約は、1つのテーブルに対して1つしか設定できません。

・主キー制約を指定したカラムに対して自動的に索引が作成されます。

4-5. 一意制約(UNIQUE制約)

テーブル内の指定したカラムには、値が重複しない一意の値しか登録できないようにする必要があります。

このことを「一意制約(UNIQUE制約)」と呼んでいます。


一意制約を指定する構文は、次の通りになります。

・表内指定(列制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型> [CONSTRAINT <制約名>] UNIQUE
    [, <カラム名2> <データ型> <制約2>] );

・表外指定(表制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
    , <カラム名1> <データ型>
    , [CONSTRAINT <制約名>] UNIQUE (カラム名1 [, カラム名2] ) );

・一意制約は、1つのテーブルに対して複数設定できます。

・一意制約を指定したカラムにNULLを設定できます。
 NULLは重複チェック対象外のためNULLを複数行に設定できます。

・一意制約を指定したカラムに対して自動的に索引が作成されます。

4-6. 外部キー制約(FOREIGN KEY制約)

複数のテーブルを結合する際に関係するキーになります。

例として、テーブルAとテーブルBを結合する際に、テーブルAにテーブルBのキーを保持しておきます。このことを外部キーと呼んでいます。

外部キーが参照するカラムのことを参照キーと呼びます。


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

テーブルAの部署CDが外部キーとして正しく機能するために、テーブルAとテーブルBの値が対応している必要があります。

そのため、テーブルBの参照キー以外の値がテーブルAに登録できないようにする必要があります。

この関係を維持するための制約を「外部キー制約(参照整合性制約)」と呼んでいます。


・外部キー制約の設定

外部キー制約を指定する構文は、次の通りになります。

・表内指定(列制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
[CONSTRAINT <制約名>] REFERENCES <参照先のテーブル名>
( <参照キーのカラム名> )
[ON DELETE { CASCADE | SET NULL }]
    [, <カラム名2> <データ型> <制約2>] );

・表外指定(表制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
    , <カラム名1> <データ型>
    , [CONSTRAINT <制約名>] FOREIGN KEY ( <外部キーのカラム名> )
        REFERENCES <参照先のテーブル名> ( <参照キーのカラム名> )
        [ON DELETE { CASCADE | SET NULL }]);

・ON DELETEオプションを指定すると、参照先テーブルのレコードを削除できます。

・外部キー制約を設定するには、参照先テーブルの参照キーに一意制約か主キー制約が設定されている必要があります。

・外部キー制約による制限

外部キー制約を設定すると参照先テーブルのデータ更新に制限がかかります。

参照元テーブルから参照先テーブルの参照キーの値を更新(UPDATE)、削除(DELETE)できなくなります。


・削除動作の変更(ON DELETE句)

外部キー制約が設定されていると参照先テーブルのデータ更新に制限がかかり、参照先テーブルのレコードを削除できません。

外部キー制約にON DELETE句を指定することで参照先テーブルのレコードを削除できるようになります。

設定する「ON DELETE CASCADE」と「ON DELETE SET NULL」で動作が変わります。


・ON DELETE CASCADE
参照先テーブルの参照キーが削除されたら参照元テーブルのレコードも削除されます。


・ON DELETE SET NULL

参照先テーブルの参照キーが削除されたら参照元テーブルの外部キーにNULLが設定されます。


4-7. チェック制約(CHECK制約)

指定した条件を満たす値のみを設定する制約です。

指定できる条件は、SELECT文でWHERE句に設定できる条件と同じになります。

条件にカラム名を設定すると、カラムに設定される値をもとにチェックされます。

・チェック制約の設定

チェック制約を指定する構文は、次の通りになります。

・表内指定(列制約)

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
[CONSTRAINT <制約名>] CHECK ( <条件式> )
    [, <カラム名2> <データ型> <制約2>] );

・表外指定(表制約):複数列をチェックする場合の構文

CREATE TABLE <テーブル名>
    ( <カラム名1> <データ型>
    , <カラム名2> <データ型>
    , [CONSTRAINT <制約名>] CHECK ( <カラム名1> < <カラム名2> );

・既存のテーブルにチェック制約を追加

既存のテーブルにチェック制約を追加する場合は、ALTER TABLE ADD文を実行します。

チェック制約を追加する構文は、次の通りになります。

ALTER TABLE <テーブル名> ADD <制約>;

・チェック制約を無効化

チェック制約はテーブルに対して操作が行われると逐一チェックされるため、大量データを投入する場合など負荷が高くなったり、処理完了まで時間がかかったりする可能性があります。

そのため、大量データを投入するなどの場合は、チェック制約を無効化することで改善する可能性があります。


チェック制約を無効化するには、ALTER TABLE DISABLE CONSTRAINT文を実行します。

チェック制約を無効化する構文は、次の通りになります。

ALTER TABLE <テーブル名> DISABLE CONSTRAINT <制約名> [ CASCADE ];

・無効化する制約に依存している別の制約がある場合は、CASCADEを指定して別の制約も合わせて無効化する必要があります。

・無効化したチェック制約を有効化

無効化したチェック制約を有効化する場合、制約に違反するデータが登録されていないことが前提になります。

違反するデータが登録されている場合は、エラーとなり制約の有効化ができません。


チェック制約を有効化するには、ALTER TABLE ENABLE CONSTRAINT文を実行します。

チェック制約を有効化する構文は、次の通りになります。

ALTER TABLE <テーブル名> ENABLE CONSTRAINT <制約名>;

5. テーブル定義の変更


作成したテーブルを何かしらの理由で変更する必要がある場合があります。

その際にALTER TABLE文を実行してテーブルの定義を変更します。


ALTER TABLE文では、次のようなことが行えます。

・カラムを追加

・カラムのデータ型、サイズの変更

・カラムのデフォルト値を変更

・カラムを削除

・カラムの未使用化

・テーブルを読み取り専用に変更

・テーブルの名前変更

5-1. テーブルにカラムを追加

既存のテーブルに新しいカラムを追加するには、ADD句を使用してカラムを追加します。

カラムを追加する構文は、次の通りになります。

ALTER TABLE <テーブル名> ADD <カラム名> <データ型> <制約> …;

・ADD句以降にCREATE TABLE文でカラムを定義する方法と同様に定義します。

・追加するカラムはテーブルの一番最後に追加されます。


レコードが格納されている場合は、次の制約が適用されます。

・追加するカラムにデフォルト値を設定しない場合は、NULLが設定されます。

・NOT NULL制約を設定する場合は、デフォルト値を定義する必要があります。

5-2. カラムのデータ型やサイズを変更

カラムの定義を変更するには、MODIFY句を使用してカラムを変更します。

カラムを変更する構文は、次の通りになります。

ALTER TABLE <テーブル名> 
    MODIFY ( <カラム名1> [ <データ型> ] [ DEFAULT <値> ] [ NULL | NOT NULL ] );

・レコードが格納されている状態でデータ型を変更するには、対象カラムの全レコードがNULLとなっている必要があります。

・レコードが格納されている状態でデータ型のサイズを変更する場合は、データサイズを大きく変更することのみが行えます。

5-3. テーブルからカラムを削除

既存のテーブルからカラムを削除するには、DROP句を使用してカラムを削除します。

カラムを変更する構文は、次の通りになります。

ALTER TABLE <テーブル名> 
DROP COLUM ( <カラム名>, <カラム名>, … ) [ CASCADE CONSTRAINTS ];

・外部キー制約で参照キーのカラムを削除する場合は、[ CASCADE CONSTRAINTS ]を指定します。

・全てのカラムを削除できません。1カラムは残す必要があります。

・レコード数が多いと削除処理に時間がかかる場合があります。

6. テーブル削除とレコード削除


不要になったテーブルを削除する方法とテーブル内のレコードのみを削除する方法について記載します。

6-1. 不要になったテーブルの削除

テーブルを削除するには、DROP TABLE文を実行します。

ORACLEの機能として、テーブル削除の際にゴミ箱に格納される機能があります。


カラムを変更する構文は、次の通りになります。

DROP TABLE <テーブル名> [ CASCADE CONSTRAINTS ] [ PURGE ];

・他のテーブルから外部キー制約で参照されている場合は、[ CASCADE CONSTRAINTS ]を指定する必要があります。

・[ PURGE ]を指定しない場合、テーブルがゴミ場に格納されます。

・[ PURGE ]を指定するとゴミ箱に格納されずテーブルが完全に削除されます。

・ゴミ箱機能

ORACLEでは、ゴミ箱機能がありテーブルを完全に削除せずゴミ箱で保持しています。

また、テーブルに索引や制約などがある場合は、それらもゴミ箱に入ります。


ゴミ箱からテーブルを復元する機能もあります。その機能をフラッシュバックドロップと呼びます。

テーブルを復元する際に索引や制約なども同時に復元されます。


ゴミ箱からテーブルを復元する構文は、次の通りになります。

FLASHBACK TABLE <テーブル名> TO BEFORE DROP;

6-2. レコードのデータをすべて削除

テーブルからレコードのみを削除する場合には、TRUNCATE TABLE文を実行します。

これによりテーブルから全てのレコードが削除されます。


カラムを変更する構文は、次の通りになります。

TRUNCATE TABLE <テーブル名> [ [ DROP [ ALL ] | REUSE ] STORAGE ] [ CASCADE ];

・1度削除したレコードはロールバック機能を使っても復元できません。

・DELETE文よりも高速でレコードが削除されます。

・DELETE文のようにWHERE句で削除対象を限定することはできません。

7. まとめ

DDL(データ定義言語)を使ったデータの操作について記載しました。

SQLクライアントツールを使うことで比較的簡単にDDLを作成できるため、それぞれのSQL文で定義するオプションが多数あると改めて認識しました。


一つ一つ手書きでDDLを作成するのは、時間がかかりますがSQLを理解する上で大切になるかと思います。

人気のタグ一覧