SQL と制約

設計を実装に落とす際は、SQLと制約を正しく使って「保存してはいけないデータ」を防ぐことが重要です。このページではSQLの基本操作から制約設計まで、実務で必要な知識を整理します。

SQLとは

SQL（Structured Query Language）は、RDBに対してデータの定義・操作・制御を行うための宣言的言語です。 「どうやって取得するか」ではなく「何を取得したいか」を書くのが特徴です。

SQLの4分類

標準SQLと方言

SQLはISO/IEC 9075で標準化されていますが、各RDBMSは独自の拡張（方言）を持ちます。

DDL（データ定義言語）

CREATE TABLE

テーブルを新規作成します。列名・データ型・制約をまとめて定義します。

CREATE TABLE customer (
    customer_id   BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_customer PRIMARY KEY (customer_id),
    CONSTRAINT uq_customer_email UNIQUE (email)
);

ALTER TABLE

既存テーブルの構造を変更します。列追加・削除・制約変更などに使います。

-- 列の追加
ALTER TABLE customer ADD COLUMN phone VARCHAR(20);

-- 列のNOT NULL化
ALTER TABLE customer ALTER COLUMN phone SET NOT NULL;

-- 制約の追加
ALTER TABLE customer
    ADD CONSTRAINT chk_email CHECK (email LIKE '%@%.%');

-- 列の削除
ALTER TABLE customer DROP COLUMN phone;

DROP TABLE

テーブルを削除します。データも構造もすべて失われるため、本番環境では慎重に扱います。

-- テーブル削除（参照されている場合はエラー）
DROP TABLE customer;

-- 依存するFKも連鎖削除（PostgreSQL）
DROP TABLE customer CASCADE;

DML（データ操作言語）

INSERT（追加）

-- 単一行の挿入
INSERT INTO customer (customer_name, email)
VALUES ('田中商事', '[email protected]');

-- 複数行の一括挿入
INSERT INTO customer (customer_name, email) VALUES
    ('鈴木工業', '[email protected]'),
    ('佐藤物産', '[email protected]');

-- SELECTの結果を挿入
INSERT INTO customer_archive (customer_id, customer_name, email)
SELECT customer_id, customer_name, email
  FROM customer
 WHERE created_at < '2024-01-01';

UPDATE（更新）

-- WHERE条件で対象行を限定
UPDATE customer
   SET email = '[email protected]'
 WHERE customer_id = 1;

-- 複数列を同時に更新
UPDATE product
   SET unit_price = unit_price * 1.1,
       updated_at = CURRENT_TIMESTAMP
 WHERE category = '食品';

DELETE（削除）

-- 条件指定で削除
DELETE FROM customer WHERE customer_id = 99;

-- 期限切れデータの一括削除
DELETE FROM session_log
 WHERE created_at < CURRENT_DATE - INTERVAL '90 days';

SELECTの基本

基本構文と処理順序

SELECTは書く順序と実行順序が異なります。実行順序を理解するとエラーの原因が分かりやすくなります。

WHERE による絞り込み

-- 比較演算子
SELECT * FROM product WHERE unit_price >= 1000;

-- BETWEEN
SELECT * FROM orders WHERE order_date BETWEEN '2025-04-01' AND '2025-04-30';

-- IN
SELECT * FROM customer WHERE customer_id IN (1, 3, 5);

-- LIKE（部分一致）
SELECT * FROM product WHERE product_name LIKE '%キーボード%';

-- IS NULL / IS NOT NULL
SELECT * FROM customer WHERE phone IS NULL;

ORDER BY（並び替え）

-- 昇順（デフォルト）
SELECT * FROM product ORDER BY unit_price;

-- 降順
SELECT * FROM orders ORDER BY order_date DESC;

-- 複合ソート
SELECT * FROM product ORDER BY category ASC, unit_price DESC;

DISTINCT（重複排除）

-- 受注実績のある顧客IDだけを取得
SELECT DISTINCT customer_id FROM orders;

GROUP BY / HAVING（集計）

-- 顧客ごとの受注件数
SELECT customer_id, COUNT(*) AS order_count
  FROM orders
 GROUP BY customer_id;

-- 受注件数が3件以上の顧客だけ
SELECT customer_id, COUNT(*) AS order_count
  FROM orders
 GROUP BY customer_id
HAVING COUNT(*) >= 3;

JOIN（結合）

正規化で分割したテーブルを結合して元の情報を取り出すのがJOINです。

サンプルデータ

-- customer テーブル
-- customer_id | customer_name
-- ----------- | -------------
-- 1           | 田中商事
-- 2           | 鈴木工業
-- 3           | 佐藤物産

-- orders テーブル
-- order_id | customer_id | order_date
-- -------- | ----------- | ----------
-- 1001     | 1           | 2025-04-01
-- 1002     | 1           | 2025-04-15
-- 1003     | 2           | 2025-05-01

INNER JOIN — 両方に存在する行だけ結合

結合条件に一致する行のみが結果に含まれます。最も基本的で使用頻度の高い結合です。

SELECT
    o.order_id,
    c.customer_name,
    o.order_date
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id;

-- 結果:
-- order_id | customer_name | order_date
-- -------- | ------------- | ----------
-- 1001     | 田中商事       | 2025-04-01
-- 1002     | 田中商事       | 2025-04-15
-- 1003     | 鈴木工業       | 2025-05-01
-- ※ 佐藤物産は受注がないため結果に含まれない

LEFT JOIN — 左テーブルの全行を保持

左側テーブルの全行を残し、右側に一致がなければNULLで埋めます。

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- 結果:
-- customer_id | customer_name | order_id
-- ----------- | ------------- | --------
-- 1           | 田中商事       | 1001
-- 1           | 田中商事       | 1002
-- 2           | 鈴木工業       | 1003
-- 3           | 佐藤物産       | NULL

CROSS JOIN — 全行の組み合わせ（直積）

SELECT c.customer_name, o.order_id
FROM customer c
CROSS JOIN orders o;

JOINの種類まとめ

複数テーブルのJOIN

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customer c    ON o.customer_id = c.customer_id
JOIN order_item oi ON o.order_id    = oi.order_id
JOIN product p     ON oi.product_id = p.product_id
ORDER BY o.order_id, oi.line_no;

サブクエリと集計関数

集計関数

-- 受注ごとの合計金額と明細行数
SELECT
    o.order_id,
    COUNT(*)                        AS item_count,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_item oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

IN サブクエリ

-- 2025年4月に受注がある顧客を取得
SELECT customer_id, customer_name
FROM customer
WHERE customer_id IN (
    SELECT customer_id FROM orders
    WHERE order_date BETWEEN '2025-04-01' AND '2025-04-30'
);

EXISTS サブクエリ

-- 受注実績がある顧客
SELECT c.customer_id, c.customer_name
FROM customer c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- 受注実績がない顧客
SELECT c.customer_id, c.customer_name
FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

相関サブクエリ

-- 各顧客の最新受注日を取得
SELECT
    c.customer_id,
    c.customer_name,
    (SELECT MAX(o.order_date)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS latest_order_date
FROM customer c;

-- 自カテゴリの平均より高い商品を抽出
SELECT product_name, category, unit_price
FROM product p
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM product
    WHERE category = p.category
);

制約の種類と実践

制約は「保存してはいけないデータ」をDB側で自動的に拒否する仕組みです。アプリ側のバリデーションとは別に、データの最後の砦として機能します。

NOT NULL — NULLを禁止する

何を防ぐか: 値が未設定のまま保存されることを防ぐ。業務上「必須」の項目に設定します。

customer_name VARCHAR(100) NOT NULL,  -- 顧客名は必須
phone         VARCHAR(20)             -- 電話は任意（NULLを許容）

UNIQUE — 重複値を禁止する

何を防ぐか: 同じ値が複数行に存在することを防ぐ。メールアドレスや業務コードに設定します。

CONSTRAINT uq_customer_email UNIQUE (email)

PRIMARY KEY — 行の一意識別子

何を防ぐか: 行の重複とNULLを防ぐ。全テーブルに必ず1つ設定します。

-- 単一列の主キー
CONSTRAINT pk_product PRIMARY KEY (product_id)

-- 複合主キー
CONSTRAINT pk_order_item PRIMARY KEY (order_id, line_no)

FOREIGN KEY — 参照整合性を保証する

何を防ぐか: 存在しない値への参照を防ぐ。

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id)

CHECK — 値の範囲・条件を制限する

何を防ぐか: 業務ルール上ありえない値の保存を防ぐ。

CHECK (unit_price >= 0)
CHECK (quantity > 0)
CHECK (status IN ('未処理', '処理中', '出荷済', 'キャンセル'))
CHECK (start_date <= end_date)

DEFAULT — 既定値を設定する

何を防ぐか: 値を指定し忘れた場合に不定な状態になることを防ぐ。

order_date  DATE      NOT NULL DEFAULT CURRENT_DATE,
status      VARCHAR(20) NOT NULL DEFAULT '未処理',
created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

複合ユニーク制約

複数列の組み合わせで一意性を保証します。

-- カテゴリ内の商品名は一意
CONSTRAINT uq_product_cat_name UNIQUE (category, product_name)

制約設計の実務ポイント

FOREIGN KEY の ON DELETE / ON UPDATE

親テーブルの行が削除・更新されたとき、子テーブルの参照行をどう扱うかを指定します。

CHECK制約の活用パターン

-- 1. 数値の範囲制限
CHECK (quantity BETWEEN 1 AND 9999)

-- 2. 列挙値の制限
CHECK (status IN ('未処理', '処理中', '出荷済', 'キャンセル'))

-- 3. 列間の比較（開始日 ≦ 終了日）
CHECK (start_date <= end_date)

-- 4. 条件付きNOT NULL（出荷済なら出荷日は必須）
CHECK (status <> '出荷済' OR shipped_date IS NOT NULL)

制約設計のガイドライン

テーブル作成の総合例

受注業務を構成する4テーブルを、ここまで学んだ制約をフルに使って定義します。

ER構成

customer 1 ──< orders 1 ──< order_item >── 1 product

1. customer（顧客）

CREATE TABLE customer (
    customer_id   BIGINT        GENERATED ALWAYS AS IDENTITY,
    customer_code VARCHAR(20)   NOT NULL,
    customer_name VARCHAR(100)  NOT NULL,
    email         VARCHAR(255)  NOT NULL,
    phone         VARCHAR(20),
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_customer       PRIMARY KEY (customer_id),
    CONSTRAINT uq_customer_code  UNIQUE (customer_code),
    CONSTRAINT uq_customer_email UNIQUE (email)
);

2. product（商品）

CREATE TABLE product (
    product_id   BIGINT         GENERATED ALWAYS AS IDENTITY,
    product_code VARCHAR(20)    NOT NULL,
    product_name VARCHAR(100)   NOT NULL,
    category     VARCHAR(50)    NOT NULL,
    unit_price   NUMERIC(10,2)  NOT NULL,
    is_active    BOOLEAN        NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_product          PRIMARY KEY (product_id),
    CONSTRAINT uq_product_code     UNIQUE (product_code),
    CONSTRAINT uq_product_cat_name UNIQUE (category, product_name),
    CONSTRAINT chk_product_price   CHECK (unit_price >= 0)
);

3. orders（受注）

CREATE TABLE orders (
    order_id     BIGINT       GENERATED ALWAYS AS IDENTITY,
    customer_id  BIGINT       NOT NULL,
    order_date   DATE         NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(20)  NOT NULL DEFAULT '未処理',
    total_amount NUMERIC(12,2),
    created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_orders          PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id) ON DELETE RESTRICT,
    CONSTRAINT chk_order_status   CHECK (
        status IN ('未処理', '処理中', '出荷済', 'キャンセル')
    ),
    CONSTRAINT chk_order_total    CHECK (total_amount >= 0)
);

4. order_item（受注明細）

CREATE TABLE order_item (
    order_id    BIGINT        NOT NULL,
    line_no     INT           NOT NULL,
    product_id  BIGINT        NOT NULL,
    quantity    INT           NOT NULL,
    unit_price  NUMERIC(10,2) NOT NULL,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_order_item    PRIMARY KEY (order_id, line_no),
    CONSTRAINT fk_item_order    FOREIGN KEY (order_id)
        REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT fk_item_product  FOREIGN KEY (product_id)
        REFERENCES product (product_id) ON DELETE RESTRICT,
    CONSTRAINT chk_item_line_no CHECK (line_no > 0),
    CONSTRAINT chk_item_qty     CHECK (quantity > 0),
    CONSTRAINT chk_item_price   CHECK (unit_price >= 0)
);

設計ポイントの解説