MySQLの制約条件とデータ整合性に関する詳細な解説
制約はデータの整合性を守る守護者であり、不適切なデータがデータベースに流入するのを防ぎます。
このレッスンでは、すべての制約の種類とその管理について、体系的に概説します。
graph TB
A[MySQL Constraints] --> B[PRIMARY KEY<br/>Primary Key Constraint]
A --> C[FOREIGN KEY<br/>Foreign Key Constraints]
A --> D[UNIQUE<br/>Unique Constraint]
A --> E[NOT NULL<br/>Non-empty constraint]
A --> F[CHECK<br/>Check Constraints]
C --> C1[CASCADE]
C --> C2[SET NULL]
C --> C3[RESTRICT]
1. 学習内容
- 主キー 主キー制約
- 外部キー 外部キー制約とカスケード
- UNIQUE 一意性制約
- NOT NULL（NULL不可）制約
- CHECK 制約のチェック (8.0+)
2. 実話
(1) 課題：不正確なデータが至る所に存在する
注文システムの稼働から6か月後、データの質は憂慮すべき状態だった。金額がマイナスの注文や、顧客と紐づいていない「ゴースト注文」、1つのユーザー名で5つのアカウントが登録されているケースなどが確認された。開発チームはアプリケーション層での検証機能の実装を試みたが、フロントエンドとバックエンドのコードがシステムのあちこちに分散していたため、常に抜け穴が生じていた。あるプロモーションキャンペーン中、ハッカーがフロントエンドの検証を迂回してマイナス金額の注文を送信し、その結果、直接的な金銭的損失が発生した。
(2) 制約付き問題を解く方法
データベースレベルでのデータ整合性の確保――PRIMARY KEYは一意性を保証し、FOREIGN KEYは関連性を保証し、CHECKは有効な範囲を保証し、UNIQUEは重複がないことを保証します。
| ディメンション | アプリケーションレベルの検証 | データベースの制約 |
|---|---|---|
| 保護範囲 | このアプリのみ | すべての接続元 |
| 迂回可能性 | フロントエンド／APIを迂回可能 | 迂回不可 |
| 保守コスト | 複数の場所に分散 | テーブル定義に一元化 |
| データセキュリティ | 中 | 最高 |
3. 5つの制限事項の概要
| 制約 | 関数 | NULL 許可 | 重複許可 |
|---|---|---|---|
| 主キー | 各行を一意に識別する | ❌ | ❌ |
| 外部キー | 他のテーブルへのリンク | ✅ | ✅ |
| 一意 | 値の重複は許されない | ✅ | ❌ |
| NOT NULL | NULLにできない | ❌ | ✅ |
| 確認 | 基準を満たしている | ✅ | ✅ |
4. 主キー
▶ 例：主キー制約
SQL
-- Single-column primary key
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50)
);
-- Composite Primary Key
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
5. 外部キー
▶ 例：外部キーとカスケード
SQL
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE -- Cascade order deletions when deleting a customer
ON UPDATE CASCADE -- Cascade update when customer ID changes
);
-- Cascade Options
-- CASCADE: Delete Synchronously/Update
-- SET NULL: Set as NULL
-- RESTRICT: Operation Rejected（Default）
-- NO ACTION: Same as RESTRICT
6. UNIQUE制約
▶ 例：一意性制約
SQL
-- Single-column, unique
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
username VARCHAR(50) UNIQUE
);
-- Composite Unique
CREATE TABLE user_roles (
user_id INT,
role_id INT,
UNIQUE (user_id, role_id)
);
7. NOT NULL 制約
▶ 例：NULL不可制約
SQL
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
description TEXT -- Allow NULL
);
8. CHECK制約 (8.0+)
▶ 例：CHECK制約
SQL
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10,2) CHECK (salary > 0),
email VARCHAR(100) CHECK (email LIKE '%@%.%')
);
-- Add CHECK Constraints
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);
-- Delete CHECK Constraints
ALTER TABLE employees DROP CHECK chk_age;
9. 制約管理
▶ 例：制約の追加・削除
SQL
-- Add a Primary Key
ALTER TABLE users ADD PRIMARY KEY (id);
-- Add a Foreign Key
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- Add a unique constraint
ALTER TABLE users ADD UNIQUE (email);
-- Delete Foreign Key
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
-- Delete the unique constraint
ALTER TABLE users DROP INDEX email;
❓ よくある質問
Q 外部キーはパフォーマンスに影響しますか？
A ある程度は影響します（書き込み時の制約チェックが原因ですが）、データ整合性を確保してくれます。高同時実行環境では、チェックをアプリケーション層で行うことも可能です。
Q NULL と UNIQUE の関係はどのようなものですか？
A UNIQUE では複数の NULL 値が許可されます（MySQL では、NULL != NULL です）。
Q CHECK制約で関数を使用できますか？
A MySQL 8.0以降では、CHECK制約での式の使用はサポートされていますが、サブクエリはサポートされていません。
Q CHECK制約は5.7で有効ですか？
A いいえ。MySQL 5.7では、CHECK制約は構文的には受け入れられますが、強制はされません。強制が行われるのはバージョン8.0.16以降からです。
Q 外部キーを削除しても、データは残りますか？
A はい。外部キーを削除しても、制約が解除されるだけで、データ自体は削除されません。
📖 まとめ
- 主キー：一意の識別子。クラスタ化インデックスが自動的に作成されます。
- FOREIGN KEY テーブル間の関係を定義します。CASCADE、SET NULL、およびRESTRICTをサポートしています。
- 一意：値は一意でなければなりません。NULL値は許可されます。
- NOT NULL NULL値を許可しない
- CHECK (8.0+) 値が基準を満たしていることを確認する
📝 練習問題
-
基本問題（難易度：⭐）：完全制約を備えたユーザーテーブルを作成してください。
-
上級演習（難易度：⭐⭐）：外部キーを作成し、カスケード削除をテストしてください。
-
課題（難易度：⭐⭐⭐）：CHECK制約を含む製品テーブルを設計し、その制約が確実に適用されることを確認してください。
