制約とキー
制約とキー
🌍 実世界のアナロジー
データベースを厳格なオフィスと考えてください：
- PRIMARY KEY = すべての社員ID。重複せず、NULLにならない
- FOREIGN KEY = 社員は実際に存在する部門に所属する必要がある
- UNIQUE = すべてのメールアドレスは一意でなければならない
- NOT NULL = 名前フィールドは空白にできない
- CHECK = 年齢は18歳から65歳の間でなければならない
- DEFAULT = ステータスが入力されていない場合、デフォルトで「有効」
制約はデータベースの「ルールガード」です。データが書き込まれる際に自動的にチェックし、ダーティデータの流入を防ぎます。
🎯 コアコンセプト
PRIMARY KEY
テーブルの各行を一意に識別します。重複は許可されず、NULLも許可されません。テーブルには1つの主キーしか持てません。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL
);
主キーは複数のカラムで構成することもできます（複合主キー）：
SQL
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
FOREIGN KEY
一方のテーブルの値が別のテーブルに存在することを保証し、参照整合性 を維持します。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
外部キーのカスケード操作：
| 操作 | 説明 |
|---|---|
ON DELETE CASCADE |
親テーブルの行が削除されると、子テーブルの関連行も削除される |
ON DELETE SET NULL |
親テーブルの行が削除されると、子テーブルの外部キーがNULLに設定される |
ON DELETE RESTRICT |
参照が存在する場合、削除は禁止される（デフォルト） |
ON UPDATE CASCADE |
親テーブルの主キーが更新されると、子テーブルの外部キーも相应に更新される |
SQL
FOREIGN KEY (department_id) REFERENCES departments(department_id)
ON DELETE SET NULL
ON UPDATE CASCADE;
UNIQUE制約
カラムのすべての値が重複しないことを保証しますが、NULLは許可されます（ほとんどのデータベースでは複数のNULLが許可されます）。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
UNIQUEとPRIMARY KEYの違い：
| 特徴 | PRIMARY KEY | UNIQUE |
|---|---|---|
| NULL値 | ❌ 許可されない | ✅ 許可される |
| 数量 | テーブルあたり1つ | 複数持てる |
| 目的 | 行の一意識別子 | カラム値の一意性 |
NOT NULL制約
カラムにNULL値を格納できないことを保証します。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);
CHECK制約
カラムの値が指定された条件を満たすことを保証します。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
salary DECIMAL(10,2) CHECK (salary > 0),
age INT CHECK (age >= 18 AND age <= 65),
gender CHAR(1) CHECK (gender IN ('M', 'F'))
);
DEFAULT値
データ挿入時にカラムの値を指定しない場合、デフォルト値が自動的に使用されます。
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'active',
hire_date DATE DEFAULT CURRENT_DATE,
salary DECIMAL(10,2) DEFAULT 5000.00
);
制約の設計原則
| 原則 | 説明 |
|---|---|
| 最小権限 | 必要な制約のみを適用し、過度に制限しない |
| ソースに近い検証 | 可能な限りカラムレベルで定義し、テーブルレベルではない |
| 命名規則 | 制約に意味のある名前を付け、保守を容易にする |
| ビジネス優先 | 制約はビジネスルールを反映すべきであり、技術的制限ではない |
📝 基本構文
SQL
-- テーブル作成時に制約を定義
CREATE TABLE テーブル名 (
カラム1 データ型 PRIMARY KEY,
カラム2 データ型 NOT NULL,
カラム3 データ型 UNIQUE,
カラム4 データ型 CHECK (条件),
カラム5 データ型 DEFAULT 値,
カラム6 データ型,
FOREIGN KEY (カラム6) REFERENCES 他テーブル(カラム)
);
-- 既存のテーブルに制約を追加
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名
UNIQUE (カラム名);
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名
CHECK (条件);
-- 制約を削除
ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;
💡 ヒント：
- 制約名はデータベース内で一意である必要があります。推奨フォーマット：
テーブル名_カラム名_制約タイプ NOT NULLはALTER TABLE ... MODIFYを使用して追加でき、ADD CONSTRAINTでは追加できません- 既存データがあるテーブルに制約を追加すると、既存データが条件を満たさない場合に失敗する可能性があります
📌 例題
例：制約付きの社員テーブルを作成
SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
department_id INT,
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL
);
-- テストデータを挿入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', 'johndoe@company.com', '2026-01-15', 8500.00, 1);
-- 以下の文は失敗します：
-- INSERT ... salary = -100 -- ❌ CHECK制約
-- INSERT ... first_name NULL -- ❌ NOT NULL制約
-- INSERT ... duplicate email -- ❌ UNIQUE制約
例：既存テーブルへの制約の追加と削除
SQL
-- CHECK制約を追加：給与は500000を超えてはならない
ALTER TABLE employees ADD CONSTRAINT chk_salary_max
CHECK (salary <= 500000);
-- UNIQUE制約を追加：電話番号は一意でなければならない
ALTER TABLE employees ADD CONSTRAINT uq_emp_phone
UNIQUE (phone);
-- 制約を削除
ALTER TABLE employees DROP CONSTRAINT chk_salary_max;
-- 外部キー制約を追加
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (department_id) REFERENCES departments(department_id);
🎬 シナリオ詳細
シナリオ1：ECサイト注文システムの制約設計
データ整合性を確保するための注文テーブルを設計します。
SQL
-- 商品テーブル
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued'))
);
-- 注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
total_amount DECIMAL(12,2) CHECK (total_amount > 0),
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'cancelled')),
CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
-- 注文アイテムテーブル（複合主キー）
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
シナリオ2：社員管理システムの制約移行
既存のテーブルに制約を追加します。
SQL
-- 既存のテーブルに制約がない場合、段階的に追加
-- 1. メール形式が正しいことを確認
ALTER TABLE employees ADD CONSTRAINT chk_email_format
CHECK (email LIKE '%@%.%');
-- 2. 入社日が合理的であることを確認
ALTER TABLE employees ADD CONSTRAINT chk_hire_date
CHECK (hire_date >= '2000-01-01' AND hire_date <= CURRENT_DATE);
-- 3. 部門の外部キー関係を確認
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (department_id) REFERENCES departments(department_id)
ON DELETE SET NULL;
-- 4. テーブルのすべての制約を表示（SQL Server）
SELECT name, type_desc
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('employees')
AND type IN ('C', 'F', 'UQ', 'PK');
❓ よくある質問
質問：テーブルに複数のUNIQUE制約を持つことはできますか？ 回答： はい。テーブルには1つの
PRIMARY KEYしか持てませんが、複数のUNIQUE制約を持つことはできます。例えば、メールアドレスと電話番号の両方にUNIQUE制約を付けることができます。
質問：外部キーはパフォーマンスを低下させますか？ 回答： 外部キーは書き込み時に追加のチェックが必要であり、書き込みパフォーマンスにわずかな影響を与えます。ただし、ほとんどのアプリケーションでは、データ整合性がわずかなパフォーマンス差よりも重要です。高書き込みシナリオでは、アプリケーション層での検証を検討してください。
質問：CHECK制約はサブクエリをサポートしていますか？ 回答： ほとんどのデータベースの
CHECK制約はサブクエリをサポートしていません。テーブル横断的な検証が必要な場合は、トリガーやアプリケーション層での実装を使用してください。
質問：DEFAULTとNOT NULLの関係は何ですか？ 回答：
NOT NULLが設定されていてDEFAULTが設定されていない場合、挿入時に明示的に値を提供する必要があります。DEFAULTが設定されている場合、挿入時にカラムを省略するとデフォルト値が自動的に使用されます。これらはしばしば一緒に使用されます。
📖 まとめ
| 制約 | 目的 | NULL許可 | 複数許可 |
|---|---|---|---|
| PRIMARY KEY | 行の一意識別子 | ❌ | テーブルあたり1つ |
| FOREIGN KEY | 他のテーブルの主キーを参照 | ✅ | ✅ |
| UNIQUE | カラム値の一意性 | ✅ | ✅ |
| NOT NULL | カラムをNULLにできない | ❌ | ✅ |
| CHECK | カラム値が条件を満たす | 定義に依存 | ✅ |
| DEFAULT | 未指定時のデフォルト値 | - | カラムあたり1つ |
- 制約はデータベースの「第一線の防衛」であり、データが書き込まれる際に自動的に検証します
- アプリケーション層だけに依存するのではなく、データベース層での制約定義を優先してください
- 保守とデバッグを容易にするため、制約に名前を付けましょう
📝 演習
- 以下の条件を持つ
customersテーブルを作成してください：主キー、NULL不可の名前、一意のメールアドレス、任意の電話番号、登録日（デフォルトは今日）、年齢（18-120）。 customersを参照する外部キーを持つordersテーブルを作成してください。注文金額は0より大きく、ステータスはpending、paid、shipped、completedのみ。- 既存の
employeesテーブルにCHECK制約を追加して、給与が最低賃金基準の2000を下回らないことを確認してください。 - 考えてみましょう：
departmentsテーブルから部門を削除し、employeesテーブルの社員がまだその部門に所属している場合、異なるON DELETEストラテジーではどうなりますか？
次のレッスン
次は 演習：複数テーブルクエリ総合 を通じてすべてを応用します。JOIN、サブクエリ、集合演算を組み合わせて実際のビジネスシナリオを解決します。
