SQL DDL:テーブル定義

概要

DDL文:データ定義言語
DataDefinitionLanguage
テーブルの作成・定義等(Create/Alter/Truncate/Drop)
実行されると暗黙のCommitが適用され、RollBackできない。

テーブル定義

Create Table

Create Table 表名
     列名1 Number(4)
     列名2 Date

既存表を基にした表作成(副問い合わせ)
Create Table 表名 ~ As Select ~ From ~

問い合わせた表をコピーして新しいテーブルを作成
・列名
・データ
・NOT NULL 制約
のみコピーされる。
・Index、他制約等はコピーされない。
・表に関する全てのオブジェクト権限が付与される

Alter Table

Alter Table 表名
  列名1 Number(4)
  列名2 Date

定義済みの表の定義変更
ALTER TABLE (表名) DROP ( (列名) );
ALTER TABLE (表名) ADD ( (列名) NVARCHAR2(1) );
ALTER TABLE (表名) MODIFY ( (列名) NVARCHAR2(100) );

DROP :データ格納済みの列を削除可能
ADD :NOT NULL制約付きの列を追加する場合は表全体を空にする必要がある
MODIFY :
データ格納済みの列の型を変える場合は列データを削除する必要がある
CHAR(10)→CHAR(5)等の文字列長の変更は、列データが変更後の文字列長以下であれば可能

ALTER TABLE 表名
  列名1 INT
  列名2 DateTime

列の追加
ALTER TABLE [表名] ADD [列名] [型] DEFAULT 値 NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] DEFAULT 2 NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] ;
※列は最後尾に追加される。

列の削除
ALTER TABLE [表名] DROP COLUMN [列名];
ALTER TABLE [TestTable] DROP COLUMN [TestColumn];

Drop Table

テーブル削除。
Drop Table 表名

【同時に削除されるオブジェクト】
・インデックス
・トリガー
・制約

【残るオブジェクト】
・ビュー(無効)
・シノニム

外部キーを持つテーブルの削除
FORIGN KEY制約を設定している場合、親表を削除できない。
この場合、制約を削除してから表を削除。
またはCascade Constraints句を利用して制約削除と同時に表を削除。

RecycleBin
DropしたテーブルはRecycleBin(ゴミ箱)に移動。
Purge句を用いるとRecycleBinに移動させずに削除

ゴミ箱削除
PURGE TABLE 表名;

表削除と同時にゴミ箱削除
DROP TABLE 表名 PURGE;

ゴミ箱表示
SELECT * FROM RECYCLEBIN;

ゴミ箱全削除
PURGE RECYCLEBIN;

RecycleBinの表を復元
FlashBack Table 表名 To Before Drop

※DROP TABLEはDDL文なのでROLL BACKできない

Viewの参照元の表をDropした場合
Viewは残るが無効扱いとなる。
シノニムも無効。

Rename

ALTER TABLE 表名 RENAME TO 新表名;

コメント

表コメント
COMMENT ON TABLE 表名 IS '~';

カラムコメント追加
COMMENT ON COLUMN (表名).(列名) IS '~'

Truncate Table

データのみ削除。Deleteより速い。
Truncate Table 表名

制約

テーブルにはデータを格納するに当たって制約を設ける事ができる。
制約の利点
・ルールの集中化(障害を防げる)
・障害の低減による開発効率の向上
・データ作成の速度は低下する(チェックが必要な為)
制約の定義
・列レベル構文
・表レベル構文
がある。
列レベル構文
列名 データ型 (CONSTRAINT 制約名) 制約内容
NOT NULL はここでしか定義できない
ALTER TABLEでは定義できない
表レベル構文
(CONSTRAINT 制約名) 制約内容
NOT NULL は定義できない
ALTER TABLEではここでしか定義できない



NOT NULL制約

NULL値が許容されない。
設定するとINDEXが自動で作成される。
PRIMARY KEY制約も同じ

Create Table 表名
  列名 型情報 NOT NULL

↓ (表定義)は不可。↑ (列定義) のみ。
Create Table 表名
  列名 型情報,
  CONSTRAINT 制約名 NOT NULL(列名)

PRIMARY KEY制約

主キーとして設定される。
主キーを一意に設定する場合に使用
・NOT NULL制約
・UNIQUE制約
が自動で課される。
設定するとINDEXが自動で作成される。
NOT NULL制約も同じ

Create Table 表名
  列名 型情報 PRIMARY KEY

Create Table 表名
  列名 型情報,
  CONSTRAINT 制約名 PRIMARY KEY(列名, 列名)

UNIQUE制約

一意でなくてはならない。重複できない。
ただしNULL値は重複して格納できる。
主キーでない列を一意に設定する場合に使用

Create Table 表名
  列名 型情報 UNIQUE

Create Table 表名
  列名 型情報,
  CONSTRAINT 制約名 UNIQUE(列名, 列名)

CHECK制約

設定値に違反できない。

Create Table 表名
  列名 型情報 CHECK(~)
※他列を参照しての制約を定義できない

Create Table 表名
  列名 型情報,
  CONSTRAINT 制約名 CHECK(~)
※他列を参照しての制約を定義できる
CHECK(A列 > B列)

(例)
Create Table TestTable
  Col1 Number(5) CHECK(0 < Col1 AND Col1 < 100)
  Col2 Varchar(3) CHECK(Col2 = 'aaa')

FOREIGN KEY制約

外部キーとして設定される。
外部キーはNULLまたは親表の主キーしか設定できない
親キーは主キー制約/UNIQUE制約の設定が必要
外部キーの参照する親表の親キーは削除できない(ON DELETE CASCADEオプション使用時除く)

foreignkey

Create Table 表名
  列名 型情報 REFERENCES 外部テーブル名(列名),
  列名 型情報 CONSTRAINT 制約名 REFERENCES 外部テーブル名(列名);
※列定義の場合はFOREIGN KEY(列名、列名)は省略する

Create Table 表名
  列名 型情報,
  列名 型情報,
  FOREIGN KEY(列名、列名) REFERENCES 外部テーブル名(列名、列名)

Create Table 表名
  列名 型情報,
  列名 型情報,
  CONSTRAINT 制約名 FOREIGN KEY(列名、列名)
  REFERENCES 外部テーブル名(列名、列名)

親(内部)テーブル:UPDATE時の挙動
Create Table 表名
  列名 型情報
  FOREIGN KEY(列名、列名) REFERENCES 外部テーブル名(列名、列名)
  ON UPDATE 設定値

親(内部)テーブルが更新された場合の挙動:設定値
RESTRICT
親テーブルが更新された場合、エラーが発生する(デフォルト)

NO ACTION
RESTRICTと同じ

ON DELETE CASCADE
親テーブルUPDATE:子テーブルも同じ値にUPDATE
親テーブルDELETE:子テーブルもDELETE

ON DELETE SET NULL
親テーブルUPDATE:子テーブルの対象列にNULL
親テーブルDELETE:子テーブルの対象列にNULL

インデックス関連制約

ALTER TABLE [dbo].[表名]
 ADD CONSTRAINT [制約名] PRIMARY KEY CLUSTERED
(
 [列名] ASC,
 ~
)
WITH (
 インデックス作成時のオプション(既定値:OFF)
 fillfactorで指定される空き領域のパーセンテージが、インデックスの中間レベルのページに適用される

 PAD_INDEX = OFF,
 
 分布統計再計算指定(既定値:OFF)
 STATISTICS_NORECOMPUTE = OFF,
 
 tempdbへの並べ替え結果格納(既定値:OFF)
 SORT_IN_TEMPDB = OFF,
 
 一意のインデックスに重複するキー値を挿入しようとした場合のエラー応答(規定値:OFF)
 ON:警告表示。一意性制約に違反する行のみが失敗する。
 OFF:エラー表示。挿入操作全体がロールバックされる。

 IGNORE_DUP_KEY = OFF,
 
 インデックス操作時、基表・関連インデックスをクエリやデータ変更で使用(既定値:OFF)
 ONLINE = OFF,
 
 行ロック許可(既定値:ON)
 ALLOW_ROW_LOCKS = ON,
 
 ページロック許可(既定値:ON)
 ALLOW_PAGE_LOCKS = ON
 
) ON [PRIMARY]

制約の追加

PRIMARY KEY制約
ALTER TABLE 表名 ADD(CONSTRAINT 制約名 PRIMARY KEY(列名));
又はALTER TABLE MODIFY(既存列名 PRIMARY KEY);

NOT NULL制約
ALTER TABLE 表名 MODIFY(列名 NOT NULL);

FOREIGN KEY制約
ALTER TABLE 表名 ADD CONSTRAINT 制約名
 FOREIGN KEY (列名) REFERENCES 表名(列名);

エラー
ALTER TABLE ADD (既存列名 PRIMARY KEY);

制約の削除

ALTER TABLE 表名 DROP CONSTRAINT 制約名;

NOT NULL制約の削除(NULL許容)
制約の変更

制約の変更


NOT NULL制約の削除(NULL許容)
ALTER TABLE 表名 MODIFY(列名 NULL);

属性

記憶域

CREATE TABLE (表名) (
  列名1 型1,
  列名2 型2
 )
 作成先表領域
 TABLESPACE (表領域名)
 「DBA 記憶域/表領域」参照
 
 サイズ
 STORAGE(
  INITIAL 5M 初期エクステントサイズ
  NEXT 10M 増分エクステントサイズ
  MINEXTENTS 4 最小エクステント数
  MAXEXTENTS UNLIMITED 最大エクステント数
  PCTINCREASE 100 エクステントサイズ拡大率
  MAXSIZE 100M 表の上限
  「DBA 記憶域/エクステント」参照
 );

読取専用属性/書込可能属性

読取専用属性
Create/Alter Table 表名
  ~
  Read Only;
※INSERT/UPDATE/DELETE/TRUNCATE/SELECT FOR UPDATE不可
※SELECT/DROP TABLEは可能

SELECT FOR UPDATEに関しては「SQL DCL:トランザクション・権限管理/読み取りの一貫性」参照

書込可能属性
Create/Alter Table 表名
  ~
  Read Write;