データ整合性を保証する制約機能

データベースにおいて、格納される情報の正確性と信頼性を維持するため、フィールドレベルでルールを定義する仕組みが用意されています。これを制約（Constraint）と呼び、不適切なデータの登録を防ぐ役割を果たします。

制約の主な種類

• NOT NULL（非空）：該当の列に NULL 値を格納することを禁止します。
• UNIQUE（唯一）：列内のすべての値が重複しないことを保証します。
• PRIMARY KEY（主キー）：行を一意に識別するための列で、NOT NULL かつ UNIQUE の性質を持ちます。
• DEFAULT（默认）：値が指定されていない場合、あらかじめ定義された初期値を挿入します。
• FOREIGN KEY（外部キー）：テーブル間の参照関係を設定し、関連するデータの一貫性を保ちます。
• CHECK（検査）：値が特定の条件を満たすか検証しますが、MySQL のバージョンによってはサポートされていない場合があります。

制約の定義と変更

テーブル作成時に制約を付与する方法と、既存のテーブル構造を変更して付与または削除する方法があります。以下に具体的な SQL 構文を示します。

NOT NULL 制約

-- テーブル作成時に設定
CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL
);

-- 既存列へ追加
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;

-- 制約の解除
ALTER TABLE users MODIFY COLUMN username VARCHAR(50);

UNIQUE 制約

-- 作成時に列レベルで設定
CREATE TABLE accounts (
    account_id INT UNIQUE AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE
);

-- 作成時にテーブルレベルで設定
CREATE TABLE accounts (
    email VARCHAR(100),
    CONSTRAINT uk_email UNIQUE (email)
);

-- 後から追加
ALTER TABLE accounts MODIFY COLUMN email VARCHAR(100) UNIQUE;

-- 削除（インデックス名を指定）
ALTER TABLE accounts DROP INDEX email;

PRIMARY KEY 制約

-- 作成時に設定
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100)
);

-- テーブルレベルでの定義
CREATE TABLE products (
    product_id INT,
    CONSTRAINT pk_products PRIMARY KEY (product_id)
);

-- 後から追加
ALTER TABLE products ADD PRIMARY KEY (product_id);

-- 削除
ALTER TABLE products DROP PRIMARY KEY;

DEFAULT 制約

-- 作成時に設定
CREATE TABLE orders (
    order_status VARCHAR(20) DEFAULT 'pending'
);

-- 後から設定
ALTER TABLE orders ALTER COLUMN order_status SET DEFAULT 'processing';

-- 削除
ALTER TABLE orders ALTER COLUMN order_status DROP DEFAULT;

FOREIGN KEY 制約

-- 作成時に設定
CREATE TABLE orders (
    user_id INT,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 後から追加
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id);

-- 削除
ALTER TABLE orders DROP FOREIGN KEY fk_user;

複数テーブル involving 查询

実運用ではデータが複数のテーブルに分散していることが一般的です。これらを結合して情報を取得する技術が必要となります。

結合查询（JOIN）

テーブル同士を共通の列に基づいて結びつけます。

• 内部結合（INNER JOIN）：両方のテーブルに一致するデータのみを抽出します。
• 外部結合（OUTER JOIN）：片方のテーブルの全データと、一致する相手のデータを抽出します。

-- 暗黙的な内部結合
SELECT u.username, o.order_id 
FROM users u, orders o 
WHERE u.user_id = o.user_id;

-- 明示的な内部結合
SELECT u.username, o.order_id 
FROM users u INNER JOIN orders o ON u.user_id = o.user_id;

-- 左外部結合（左表を基準）
SELECT u.username, o.order_id 
FROM users u LEFT OUTER JOIN orders o ON u.user_id = o.user_id;

-- 右外部結合（右表を基準）
SELECT u.username, o.order_id 
FROM orders o RIGHT OUTER JOIN users u ON o.user_id = u.user_id;

サブ查询

SQL 文の中に別の SELECT 文をネストさせて使用します。例えば、特定の基準を満たすレコードを検索する際に有用です。

-- 例：山田さんよりも給与が高い従業員を検索
-- 段階的な查询
SELECT salary FROM employees WHERE name = 'Yamada'; -- 結果：50000 と仮定
SELECT * FROM employees WHERE salary > 50000;

-- サブ查询を用いた一括処理
SELECT * FROM employees 
WHERE salary > (SELECT salary FROM employees WHERE name = 'Yamada');

トランザクション制御

トランザクションとは、複数のデータベース操作を 하나의論理的な単位としてまとめる仕組みです。これにより、処理の途中にエラーが発生した場合でも、データの不整合を防ぐことができます。

トランザクションの概念

例えば、口座間の送金処理を考えます。A さんの口座から減算し、B さんの口座に加算するという 2 つの操作が必要です。もし減算後にシステム障害が発生し、加算が行われなかった場合、金額が消失してしまいます。トランザクションを用いることで、これらの操作を「すべて成功」か「すべて失敗（ロールバック）」のいずれかに統一できます。

基本構文

-- トランザクションの開始
START TRANSACTION;
-- または
BEGIN;

-- 処理の確定
COMMIT;

-- 処理の取り消し
ROLLBACK;

ACID 特性

信頼性の高いトランザクションシステムは、以下の 4 つの特性を満たす必要があります。

• 原子性（Atomicity）：トランザクション内の操作はすべて実行されるか、すべて実行されないかのどちらかです。
• 一貫性（Consistency）：トランザクションの前後でデータベースの状態が整合性を保っている必要があります。
• 隔离性（Isolation）：同時に実行される複数のトランザクションが互いに干渉しないように制御されます。
• 永続性（Durability）：一度コミットされた変更は、障害が発生しても失われません。

自動コミットの設定

MySQL ではデフォルトで各 SQL 文が実行されるたびに自動的にコミットされます。この動作は変数で確認および変更可能です。

-- 自動コミット状態の確認（1:ON, 0:OFF）
SELECT @@autocommit;

-- 自動コミットを無効化（手動コミットモードへ）
SET @@autocommit = 0;