はじめに
基本情報技術者試験の学習をしていた為、
更新が遅れました。
本記事では、PostgreSQLのテーブル制約の種類と使い方を
SQLiteとの比較を交えながら備忘録としてまとめます。
1. 制約の種類一覧
| 制約 | キーワード | SQLiteとの違い |
|---|---|---|
| 主キー制約 | PRIMARY KEY |
ほぼ同じ |
| NOT NULL制約 | NOT NULL |
同じ |
| UNIQUE制約 | UNIQUE |
同じ |
| CHECK制約 | CHECK |
ほぼ同じ |
| DEFAULT制約 | DEFAULT |
ほぼ同じ |
| 外部キー制約 | FOREIGN KEY |
デフォルトで有効（SQLiteは無効） |
| 除外制約 | EXCLUDE |
PostgreSQL独自（SQLiteにはない） |
2. PRIMARY KEY（主キー制約）
-- 列レベル
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- 自動採番はSERIALを使う
name TEXT
);
-- テーブルレベル（複合主キー）
CREATE TABLE order_parts (
order_id INTEGER,
part_id INTEGER,
PRIMARY KEY (order_id, part_id)
);
SQLiteとの違い
| 項目 | SQLite | PostgreSQL |
|---|---|---|
| 自動採番 | INTEGER PRIMARY KEY |
SERIAL PRIMARY KEY |
| 大きな自動採番 | INTEGER PRIMARY KEY |
BIGSERIAL PRIMARY KEY |
| UUID主キー | 非推奨 | UUID DEFAULT gen_random_uuid() |
PostgreSQLではSERIALの代わりにGENERATED ALWAYS AS IDENTITYも使えます。
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
より標準SQLに近い書き方です。
3. NOT NULL制約
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER -- NULLを許可
);
SQLiteと同じ書き方で使えます。
4. UNIQUE制約
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
name TEXT
);
-- 複合UNIQUE
CREATE TABLE schedules (
user_id INTEGER,
date DATE,
UNIQUE (user_id, date)
);
SQLiteとの違い
PostgreSQLのUNIQUE制約には部分インデックスを使った
条件付きUNIQUEが設定できます。
-- activeがTRUEの行だけUNIQUEにする（PostgreSQL独自）
CREATE UNIQUE INDEX unique_active_email
ON users (email)
WHERE active = TRUE;
5. CHECK制約
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC CHECK (price >= 0),
rank TEXT CHECK (rank IN ('A', 'B', 'C'))
);
SQLiteとほぼ同じ書き方で使えます。
PostgreSQL独自の書き方
-- 制約に名前を付けられる（エラーメッセージが分かりやすくなる）
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price NUMERIC CONSTRAINT price_non_negative CHECK (price >= 0)
);
エラー時にprice_non_negativeという名前が表示されるため
原因を特定しやすくなります。
6. DEFAULT制約
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
message TEXT NOT NULL,
level TEXT DEFAULT 'INFO',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 現在日時
is_read BOOLEAN DEFAULT FALSE
);
SQLiteとの違い
| 項目 | SQLite | PostgreSQL |
|---|---|---|
| 現在日時 | datetime('now', 'localtime') |
CURRENT_TIMESTAMP |
| 真偽値デフォルト | DEFAULT 0 |
DEFAULT FALSE |
| UUID自動生成 | なし | DEFAULT gen_random_uuid() |
7. FOREIGN KEY（外部キー制約）
-- 親テーブル
CREATE TABLE parts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
-- 子テーブル
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
part_id INTEGER NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
FOREIGN KEY (part_id) REFERENCES parts(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
SQLiteとの最大の違い
| 項目 | SQLite | PostgreSQL |
|---|---|---|
| デフォルトの有効・無効 |
無効（毎回PRAGMAが必要） |
有効（設定不要） |
| 有効化方法 | PRAGMA foreign_keys = ON |
不要 |
PostgreSQLでは外部キー制約がデフォルトで有効です。
SQLiteのように毎回PRAGMAを実行する必要がありません。
8. EXCLUDE制約（PostgreSQL独自）
SQLiteにはないPostgreSQL独自の制約です。
特定の条件で重複する行を禁止できます。
-- 同じ部屋の予約が重複しないようにする
CREATE TABLE reservations (
room_id INTEGER,
period TSRANGE, -- 時間範囲型
EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
範囲型と組み合わせて予約システムの重複チェックなどに使われます。
通常のCHECK制約では難しい「範囲の重複禁止」を実現できます。
9. 制約の組み合わせ例
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
emp_code TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
department TEXT NOT NULL DEFAULT '未配属',
salary NUMERIC(10, 2) NOT NULL CHECK (salary >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
10. SQLiteとPostgreSQLの制約比較まとめ
| 制約 | SQLite | PostgreSQL |
|---|---|---|
| 自動採番 | INTEGER PRIMARY KEY |
SERIAL / BIGSERIAL
|
| 外部キーの有効化 |
PRAGMAが必要 |
不要（デフォルト有効） |
| BOOLEAN型 |
INTEGER（0/1） |
BOOLEAN（TRUE/FALSE） |
| 日時のデフォルト | datetime('now','localtime') |
CURRENT_TIMESTAMP |
| 制約への命名 | 非推奨 | CONSTRAINT 名前 CHECK(...) |
| 除外制約 | なし | EXCLUDE USING GIST |
| 部分インデックス | 対応 | 対応（より高機能） |
まとめ
| ポイント | 内容 |
|---|---|
| 外部キーはデフォルト有効 | SQLiteと異なりPRAGMA不要 |
自動採番はSERIAL
|
SQLiteのINTEGER PRIMARY KEYと同等 |
| 制約に名前を付けられる | エラー追跡がしやすくなる |
EXCLUDE制約はPostgreSQL独自 |
範囲の重複禁止に有効 |
| 型が厳密 | BOOLEANやTIMESTAMPが専用型として使える |
さいごに
SQLiteでの実務経験があれば
PostgreSQLの制約は比較的スムーズに理解できる気がします。
ただ、SQLiteはサーバーレスのDBMSなので
SQLiteにはない機能も多いです。
今回、まとめた内容以外にも
オリジナルの機能もあるので学習をすすめます。