【Oracle】CREATE TABLE 完全ガイド｜テーブル作成・CTAS・列制約・ストレージオプションまで解説

-- 最もシンプルな例
CREATE TABLE products (
    product_id   NUMBER(10)     NOT NULL,
    product_name VARCHAR2(200)  NOT NULL,
    price        NUMBER(12, 2)  DEFAULT 0 NOT NULL,
    category     VARCHAR2(50),
    created_at   DATE           DEFAULT SYSDATE NOT NULL,
    description  CLOB,
    CONSTRAINT products_pk PRIMARY KEY (product_id),
    CONSTRAINT products_name_uk UNIQUE (product_name),
    CONSTRAINT products_price_chk CHECK (price >= 0)
);

-- 主なデータ型の選び方
-- NUMBER(p, s): 数値。p=精度（有効桁数）、s=スケール（小数点以下桁数）
--   NUMBER(10):     整数 10桁
--   NUMBER(12, 2):  小数点2桁（金額など）
--   NUMBER:         精度未指定（38桁まで）
-- VARCHAR2(n):      可変長文字列。n はバイト数（BYTE）または文字数（CHAR）
--   VARCHAR2(200 CHAR): 200文字（マルチバイト文字も200文字として数える）
--   VARCHAR2(200 BYTE): 200バイト（3バイト文字は約66文字）
-- CHAR(n):          固定長文字列（未使用部分はスペースパディング）
-- DATE:             日付＋時刻（秒まで）
-- TIMESTAMP:        日付＋時刻（ミリ秒・タイムゾーン対応）
-- CLOB:             大量テキスト（最大 128TB）
-- BLOB:             バイナリデータ（最大 128TB）
-- BOOLEAN:          真偽値（Oracle 23ai 以降）

-- テーブル作成後の確認
DESCRIBE products;              -- 列定義を確認する
SELECT * FROM USER_TABLES WHERE table_name = 'PRODUCTS';  -- テーブル情報を確認する

-- ① インライン制約（列の定義と同じ行に書く）
-- シンプルな制約に適している
CREATE TABLE orders (
    order_id    NUMBER(10)    CONSTRAINT orders_pk PRIMARY KEY,   -- インライン PK
    customer_id NUMBER(10)    NOT NULL,                           -- NOT NULL
    status      VARCHAR2(20)  DEFAULT 'PENDING'                   -- DEFAULT 値
                              CHECK (status IN ('PENDING','SHIPPED','CANCELLED')),
    order_date  DATE          DEFAULT SYSDATE NOT NULL,
    amount      NUMBER(12, 2) NOT NULL
);

-- ② テーブルレベル制約（複合主キー・複合 UNIQUE に必須）
CREATE TABLE order_items (
    order_id    NUMBER(10) NOT NULL,
    item_seq    NUMBER(5)  NOT NULL,
    product_id  NUMBER(10) NOT NULL,
    quantity    NUMBER(10) NOT NULL,
    unit_price  NUMBER(12, 2) NOT NULL,
    -- テーブルレベルの複合主キー
    CONSTRAINT order_items_pk PRIMARY KEY (order_id, item_seq),
    -- テーブルレベルの外部キー
    CONSTRAINT oi_order_fk  FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT oi_product_fk FOREIGN KEY (product_id) REFERENCES products (product_id),
    -- テーブルレベルの UNIQUE（複合 UNIQUE）
    CONSTRAINT oi_order_prod_uk UNIQUE (order_id, product_id),
    -- テーブルレベルの CHECK
    CONSTRAINT oi_qty_chk CHECK (quantity > 0)
);

-- 制約の命名規則（例）:
-- テーブル名_PK: 主キー
-- テーブル名_カラム名_UK: UNIQUE
-- テーブル名_カラム名_FK: 外部キー
-- テーブル名_カラム名_CHK: CHECK
-- テーブル名_カラム名_NN: NOT NULL（CHECK 制約として定義する場合）

-- DEFERRABLE 制約（トランザクション内での制約チェックの延期が可能）
-- 相互参照がある場合や大量データロード時に使用
CONSTRAINT orders_cust_fk FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    DEFERRABLE INITIALLY DEFERRED   -- デフォルトで遅延評価
-- DEFERRABLE INITIALLY IMMEDIATE: デフォルトは即時評価（SET CONSTRAINTS DEFERRED で変更可）

-- 既存テーブルの全データをコピーして新テーブルを作成する
CREATE TABLE employees_backup AS
    SELECT * FROM employees;
-- ※ 制約（PRIMARY KEY・NOT NULL 以外）はコピーされない
-- ※ インデックスもコピーされない → 必要なら別途作成する

-- 特定列・条件でフィルタしてコピーする
CREATE TABLE high_salary_employees AS
    SELECT employee_id, last_name, salary, department_id
    FROM employees
    WHERE salary > 8000;

-- 列名を変更して CTAS する（AS で別名指定）
CREATE TABLE dept_salary_summary AS
    SELECT department_id,
           COUNT(*) AS emp_count,
           AVG(salary) AS avg_salary,
           MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id;

-- 構造だけコピーしてデータはコピーしない（WHERE 1=2 で 0件）
CREATE TABLE orders_template AS
    SELECT * FROM orders WHERE 1 = 2;
-- → テーブル定義（列・データ型）はコピーされるが行データは0件

-- CTAS でタブルスペースを指定する
CREATE TABLE employees_backup
    TABLESPACE data_ts
AS
    SELECT * FROM employees;

-- NOLOGGING で高速 CTAS（大量データ向け・REDO ログを減らす）
CREATE TABLE large_table_copy
    NOLOGGING
AS
    SELECT * FROM large_source_table;
-- NOLOGGING はバックアップ後のリカバリ性に注意（バックアップ後に実行推奨）

-- ストレージオプションを指定した本番テーブルの作成例
CREATE TABLE transactions (
    txn_id      NUMBER(15)   NOT NULL,
    account_id  NUMBER(10)   NOT NULL,
    txn_date    DATE         DEFAULT SYSDATE NOT NULL,
    amount      NUMBER(15, 2) NOT NULL,
    txn_type    VARCHAR2(20) NOT NULL,
    CONSTRAINT transactions_pk PRIMARY KEY (txn_id)
)
-- 表領域の指定（テーブルを格納する表領域）
TABLESPACE data_ts
-- インデックスは別表領域に作成（USING INDEX で指定する場合）
-- USING INDEX TABLESPACE index_ts

-- PCTFREE: 将来の UPDATE のために各ブロックに残す空き容量（%）
-- デフォルト 10。UPDATE が多いテーブルは大きめに設定
PCTFREE 20

-- PCTUSED: ブロックに新しい行を挿入できる最低使用率（%）（デフォルト 40）
-- ASSM（Automatic Segment Space Management）を使う場合は無視される
-- PCTUSED 40

-- INITRANS: 各ブロックの初期トランザクションスロット数（デフォルト 1）
-- 並列 DML が多い場合は大きめに設定（最大 255）
INITRANS 4

-- STORAGE: エクステントのサイズ設定（ASSM ではほぼ不要）
STORAGE (
    INITIAL     1M         -- 最初のエクステントサイズ
    NEXT        1M         -- 2番目以降のエクステントサイズ
    MINEXTENTS  1          -- 最小エクステント数
    MAXEXTENTS  UNLIMITED  -- 最大エクステント数（UNLIMITED: 無制限）
)

-- COMPRESS: データブロックを圧縮する（ストレージ節約）
-- COMPRESS FOR OLTP や COMPRESS FOR QUERY HIGH などもある（EE 機能）
-- COMPRESS

-- CACHE / NOCACHE: バッファキャッシュへの優先度
-- CACHE: バッファキャッシュの先頭に配置（小テーブルの高速アクセス）
-- NOCACHE: バッファキャッシュを使わない（大テーブルのシーケンシャルスキャン向け）
NOCACHE
;

-- ストレージパラメータを確認する
SELECT table_name, tablespace_name, pct_free, pct_used,
       ini_trans, max_trans, initial_extent, next_extent,
       min_extents, max_extents, compression
FROM USER_TABLES
WHERE table_name = 'TRANSACTIONS';

-- テーブルのすべての行を削除する（高速・ロールバック不可）
TRUNCATE TABLE orders;

-- ストレージを保持したまま削除する（REUSE STORAGE: デフォルト DROP STORAGE）
TRUNCATE TABLE orders REUSE STORAGE;
-- DROP STORAGE（デフォルト）: エクステントを解放してハイウォーターマークをリセット
-- REUSE STORAGE: エクステントを保持（次の INSERT が速くなるが容量は返却しない）

-- カスケード削除（12c 以降: 外部キーで参照されている場合も削除可）
-- 通常は外部キー制約があると TRUNCATE できない
-- → 外部キー制約を一時的に DISABLE して TRUNCATE するか、子テーブルを先に TRUNCATE する

-- DROP TABLE と TRUNCATE の違い
-- DROP TABLE: テーブル定義ごと削除（PURGE を付けるとゴミ箱にも入らない）
DROP TABLE old_logs PURGE;  -- テーブルを完全に削除する
-- TRUNCATE: テーブル定義を残してデータのみ削除（再利用可能）
TRUNCATE TABLE temp_data;