SQL DDL：テーブル定義

Create Table 表名
　　 　　列名１ Number(4)
　　 　　列名２ Date

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

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

確認
show create table 表名

Alter Table 表名
　　列名１ Number(4)
　　列名２ Date

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

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

ALTER TABLE 表名 ADD COLUMN 列名 データ型
UPDATE 表 SET jsonb列名 = jsonb列名 || '{“jsonb列内列名”:”初期値”}';
ALTER TABLE 表名 DROP COLUMN 列名
ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 型名

ALTER TABLE 表名 ADD COLUMN 列名 データ型 AFTER 列名;
ALTER TABLE 表名 DROP COLUMN 列名

データ型変更
ALTER TABLE 表名 MODIFY 列名 データ型;

移動(データ型変更)
ALTER TABLE 表名 MODIFY 列名 データ型 AFTER 列名;

主キー変更
ALTER TABLE 表名 DROP PRIMARY KEY, ADD PRIMARY KEY (列名1,列名2);

デフォルト値設定
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 値;

カラムコメント変更
ALTER TABLE 表名 CHANGE COLUMN 列名 列名 int(5) NOT NULL COMMENT 'コメント';

連番初期化
ALTER TABLE 表名 auto_increment = 1;

ALTER TABLE 表名
　　列名１ INT
　　列名２ 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 表名

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

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

外部キーを持つテーブルの削除
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は残るが無効扱いとなる。
シノニムも無効。

テーブル削除
Drop Table 表名

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

ALTER TABLE 表名 RENAME TO 新表名;

CREATE TEMPORARY TABLE test_temp (
　id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
　〜,
　PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE test_temp AS SELECT * FROM 既存テーブル WHERE 〜;

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

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

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'public'
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME

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

Create Table 表名
　　列名 型情報 NOT NULL

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

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

Create Table 表名
　　列名 型情報 PRIMARY KEY

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

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

Create Table 表名
　　列名 型情報 UNIQUE

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

設定値に違反できない。

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')

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

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);

NOT NULL制約
ALTER TABLE 表名 ALTER COLUMN 列名 SET NOT NULL

FOREIGN KEY制約
ALTER TABLE 表名 ADD CONSTRAINT 制約名 FOREIGN KEY (列名1, 列名2)

外部キー制約無効
set FOREIGN_KEY_CHECKS=0;
外部キー制約有効
set FOREIGN_KEY_CHECKS=1;

ALTER TABLE 表名 DROP CONSTRAINT 制約名;

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

ALTER TABLE 表名 DROP CONSTRAINT 制約名;

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

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

NOT NULL制約の削除(NULL許容)
ALTER TABLE 表名 ALTER COLUMN 列名 DROP NOT 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;