SQLのDDLについて

はじめに

こんにちは！今回は、SQLのDDL（Data Definition Language）に注目します。DDLはデータベースやテーブル、ビュー、インデックスなど、データベースの構造を作り上げるための言語です。データベース開発の初期段階やメンテナンス時に重要な役割を果たすため、ぜひ押さえておきましょう。

本文

1. SQL-DDLとは？

SQL-DDL（Data Definition Language） は、データベースの構造やオブジェクトを定義・変更・削除するためのSQL文です。主に以下のコマンドが含まれます。

• CREATE: 新しいオブジェクト（データベース、テーブル、ビュー、インデックス、ストアドプロシージャ等）の作成
• DROP: 既存オブジェクトの削除
• ALTER: 既存オブジェクトの構造や設定を変更

これらを使いこなすことで、データベースの基本的な枠組みを構築できます。

2. データベース定義

複数のテーブルやビューなどをまとめて管理する単位が「データベース」です。ほとんどのRDBMSで、独立したデータベースを複数作成・使用できます。

-- 新しいデータベースの作成例
CREATE DATABASE 社員管理DB;

-- データベースを削除
DROP DATABASE 社員管理DB;

多くのDBMSでは、まず CREATE DATABASE を実行してデータベースを用意し、その上でテーブルなどを定義します。

3. テーブルの作成と制約

テーブルはデータを格納する基本要素で、CREATE TABLE で定義します。列（カラム）名とデータ型、必要に応じて制約を設定します。

-- 構文
CREATE TABLE テーブル名 (
    列名 データ型 [列の制約],
    ...n
    [テーブル全体の制約]
);

-- 例
CREATE TABLE 社員 (
    社員番号      INT          PRIMARY KEY,
    社員名        NVARCHAR(50) NOT NULL,
    所属          NVARCHAR(50) NOT NULL,
    入社日        DATE         NOT NULL,
    メールアドレス NVARCHAR(100) UNIQUE
);

3.1 データ型

• 整数型（INT, INTEGER など）
• 文字列型（CHAR, VARCHAR, NCHAR など）
• 日付時刻型（DATE, TIME, TIMESTAMP など）
• 数値型（DECIMAL, NUMERIC, FLOAT など）

DBMSによってサポートする型や名称が異なる場合があります。

3.2 テーブル制約

• PRIMARY KEY: テーブルの主キー（重複やNULLを許さない）
• UNIQUE: 一意性制約（重複を許さないがNULLは可）
• NOT NULL: 列にNULLを入れられない
• CHECK: 値の範囲など条件を指定
• FOREIGN KEY: 他テーブルの主キーを参照する外部キー制約

参照制約（外部キー）を設定する場合に、ON DELETE や ON UPDATE の動作を指定することも可能です。

オプション	意味
NO ACTION (デフォルト)	参照違反が起きたらエラーを生成して操作をキャンセル
CASCADE	親レコードの更新・削除を子レコードに連動させる
SET NULL	親レコードが消えたら子レコード側の外部キーをNULLに設定
SET DEFAULT	親レコードが消えたら子レコード側の外部キーを既定値に設定

3.3 テーブル作成の具体例

CREATE TABLE 顧客 (
    顧客番号       INTEGER PRIMARY KEY,
    氏名          NCHAR(20) NOT NULL,
    住所          NVARCHAR(100),
    郵便番号      CHAR(8) REFERENCES 担当支店(郵便番号),
    電話番号      CHAR(12),
    メールアドレス VARCHAR(30) UNIQUE
);

上記例では「氏名は必須（NOT NULL）、郵便番号は担当支店テーブルを参照（FOREIGN KEY制約）、メールアドレスは一意（UNIQUE）」など、さまざまな制約を設定しています。

4. ビュー（View）

ビューは 「仮想的なテーブル」 です。実際のデータは持たず、参照されるたびに定義されたSELECT文が実行されます。特定の列や行だけを参照する簡易化や制限、テーブル結合を隠蔽するなどの抽象化が可能となり、セキュリティや可読性の向上に役立ちます。

-- 構文
CREATE VIEW ビュー名 [(列のリスト)]
AS
(
    SELECT文
)
[WITH [CASCADED | LOCAL] CHECK OPTION];

-- 例
CREATE VIEW 営業部社員 (社員番号, 社員名) AS
  SELECT 社員番号, 社員名
  FROM 社員
  WHERE 所属 = '営業部';

上記のように定義されたビュー「営業部社員」を SELECT * FROM 営業部社員 のように参照すると、実際には「所属=営業部」の社員テーブルが参照される仕組みです。

4.1 WITH CHECK OPTION

ビューを更新可能ビューにしたい場合、WITH CHECK OPTION を付与することで、ビューの定義条件に合致しない更新を防ぐことができます。これは、ビュー経由で実行される UPDATE や INSERT などの操作が、常にビューの検索条件を満たしたままになるように強制するためのオプションです。

CREATE VIEW 営業部社員 (社員番号, 社員名)
AS
    SELECT 社員番号, 社員名
    FROM 社員
    WHERE 所属 = '営業部'
WITH CHECK OPTION;

ビュー定義内で WITH CHECK OPTION を指定すると、たとえばビューを通じて「所属」を「総務部」に変更しようとした場合、定義条件（WHERE 所属 = '営業部'）を満たさなくなるためエラーとなり、更新が拒否されます。

CASCADED と LOCAL の違い

WITH CHECK OPTION には以下の2種類の動作モードがあります（DBMSやバージョンによってサポート状況が異なる場合があります）

• CASCADED
  このビューを元にして定義された他のビューにも制約が連鎖し、すべての関連ビューで設定されている条件を満たす必要があります。

  CREATE VIEW 営業部社員 (社員番号, 社員名)
  AS
      SELECT 社員番号, 社員名
      FROM 社員
      WHERE 所属 = '営業部'
  WITH CASCADED CHECK OPTION;
  

  もし「営業部社員ビュー」を基に別のビューが定義されている場合も、そちらの条件まで含めてすべてクリアする必要があります。

• LOCAL
  現在のビューが持つ条件だけを考慮します。親ビュー・子ビューなどのほかの制約は参照しません。

  CREATE VIEW 営業部社員 (社員番号, 社員名)
  AS
      SELECT 社員番号, 社員名
      FROM 社員
      WHERE 所属 = '営業部'
  WITH LOCAL CHECK OPTION;
  

  上記のように定義されたビュー「営業部社員」を通じてデータを更新するとき、INSERTやUPDATEの操作は必ず所属='営業部'という条件に適合する行だけに対して許可されます。

  たとえば、以下のように社員名を更新すると、実際の「社員」テーブルの該当行が変更されますが、所属を他部門に変更しようとするとエラーになり、更新が拒否されます。

  -- 営業部社員ビュー経由の社員名更新例
  UPDATE 営業部社員
    SET 社員名 = '山田 花子'
  WHERE 社員番号 = 1001;
  

これにより、ビューを通じて発生し得る不正な更新や無効なデータの挿入を制限できます。実運用では、ビューに対する更新を許可するかどうかは要件次第ですが、WITH CHECK OPTION を使うことでビジネスルールをより厳密に保証できるため便利です。

5. インデックス（索引）

インデックスは、検索・更新を高速化するための仕組みです。DBMSはインデックスを利用して必要な行を効率的に見つけられます。

-- 構文
CREATE INDEX インデックス名
ON テーブル名 (
    列名1,
    列名2,
    ...n
);

-- 例
CREATE INDEX IDX_顧客_郵便番号
  ON 顧客(郵便番号, 顧客番号);

複数の列を指定したインデックスは連結インデックス（複合インデックス）と呼ばれます。ただし、インデックスが増えすぎるとINSERT/UPDATE/DELETEが遅くなるデメリットもあるため、必要性を検討しましょう。

6. カーソル

カーソルは、SELECT文で得られる複数行の結果を1行ずつ処理する仕組みです。主に手続き的な操作を行うストアドプロシージャやアプリケーション内で利用します。

1. DECLARE: カーソルを宣言
2. OPEN: カーソルを開く
3. FETCH: 1行ずつ取得
4. CLOSE: カーソルを閉じる

-- 構文
DECLARE カーソル名 CURSOR FOR
    SELECT文
[FOR {READ ONLY | UPDATE [OF 列名[, ...n]]}];

-- 例
DECLARE 顧客カーソル CURSOR FOR
  SELECT 顧客番号, 氏名 FROM 顧客 WHERE ...

-- 構文
OPEN カーソル名;
FETCH カーソル名 INTO :ホスト変数;
CLOSE カーソル名;

-- 例
OPEN 顧客カーソル;
FETCH 顧客カーソル INTO :顧客番号, :顧客名; -- データが無くなるまでFETCHを繰り返す
CLOSE 顧客カーソル;

カーソルを使うと、WHERE CURRENT OF カーソル名 を使って FETCHした行を直接更新・削除できます。

-- 構文
UPDATE 表名
   SET 列名 = 値 [, ...n]
 WHERE CURRENT OF カーソル名;

7. ロール（ROLE）

ロールは、ユーザーグループや権限の集合を一括管理する仕組みです。複数のユーザに同じ権限を割り当てたい場合、個別のGRANT/REVOKEよりロールを使う方が便利です。

-- 構文
CREATE ROLE ロール名 [WITH オプション]

-- 例
CREATE ROLE 営業チーム;
GRANT SELECT, INSERT ON 受注 TO 営業チーム;
GRANT 営業チーム TO U1, U2; -- ユーザU1, U2に一括権限付与

8. ドメイン定義

SQLでのドメインは、カスタムデータ型のようなものです。特定の型やCHECK制約をまとめて「ドメイン」として定義し、複数のテーブルで使い回すことができます。

CREATE DOMAIN メールアドレスドメイン AS VARCHAR(100)
    CHECK (VALUE LIKE '%@%' );

上記のように定義すれば、いくつものテーブルで メールアドレスドメイン を使用し、メール形式のCHECK制約を一括で適用できます。

9. トリガー

トリガーは、テーブルの行がINSERT/UPDATE/DELETEされるときに、自動的に起動する仕組みです。関連するデータの整合性維持やログ記録などに利用します。

-- 構文
CREATE TRIGGER <トリガー名>
     <トリガー動作時期> <トリガー事象>
     ON <表名>
     [REFERENCING <遷移表または遷移変数リスト>]
     <非トリガー動作>;

-- 例
CREATE TRIGGER TRG_在庫チェック
AFTER UPDATE OF 引当済数量 ON 在庫
REFERENCING NEW ROW AS CHKROW
FOR EACH ROW
WHEN (CHKROW.実在庫数量 - CHKROW.引当済数量 <= CHKROW.基準在庫数量)
BEGIN ATOMIC
    CALL PARTSORDER(CHKROW.部品番号);
END

• <トリガー動作時期> : BEFORE / AFTER / INSTEAD OF
• <トリガー事象> : INSERT / UPDATE / DELETE（列指定可）
• REFERENCING : OLD ROW / NEW ROW（更新前後の行）、OLD TABLE / NEW TABLE（更新前後の全行）を参照する変数を指定
• WHEN : 条件式が真のときにトリガーを実行
• CALL PARTSORDER : ストアドプロシージャを呼び出して、追加の処理を行う

まとめ

SQL-DDLは、データベースの骨格を定義・変更・削除するための強力な仕組みです。テーブルやビュー、インデックス、トリガーなどを適切に設計・実装することで、安全かつ効率的なデータ管理を実現できます。

• CREATE/ALTER/DROP でオブジェクトのライフサイクルを管理
• 制約やドメイン を使いこなして、データの整合性と保守性を高める
• ビューやカーソル で複雑なロジックや分かりやすい操作を提供
• トリガー で更新の自動連動やログ記録を実装

これらを理解し使いこなすことで、DB開発や運用をスムーズに行えるようになります。次回はDMLやDCLなど、他のSQLカテゴリーについても深掘りしてみます。