SQL制約一覧｜主キー・UNIQUE・CHECK・NOT NULLの追加と削除

SQLの制約とは、テーブルに入れてよいデータの条件を決め、条件に合わないデータをデータベース自身が受け付けないようにするルールです。 代表的なものは PRIMARY KEY（主キー）・UNIQUE・NOT NULL・CHECK・FOREIGN KEY（外部キー）・DEFAULT の6種類で、CREATE TABLE のときにも、あとから ALTER TABLE でも追加・削除できます。

この記事では、6種類を 「役割／NULL可否／1テーブルに何個／追加構文／削除構文」の1枚の一覧表 にまとめ、追加・削除の ALTER TABLE 文を MySQL／PostgreSQL／Oracle／SQL Server で並べて 比べます。さらに、各制約に違反したときの 実際のエラーメッセージ と、既存データが違反していて制約を追加できないときの対処 を、MySQL 8.0.46 と PostgreSQL 16.15 で動かした出力つきで紹介します。

SQL制約6種の早見表

• DEFAULT は「入れてはいけない値を防ぐ」ルールではありませんが、列の定義に付ける決まりごととして、制約と一緒に説明されることが多いためここに含めています。SQL Server では DEFAULT も名前付きの制約として扱われます。
• MySQL と PostgreSQL の構文とエラーは今回実行して確かめた内容です。Oracle と SQL Server は手元に実行環境がないため未実行 で、一般に知られた仕様として載せています（SQL Server の WITH NOCHECK と DEFAULT ... FOR 列 は、Microsoft Learn の ALTER TABLE table_constraint のページを開いて確認しました）。

CREATE TABLEで6種類の制約をまとめて定義する

まず、6種類すべてを含むテーブルを作ります。制約には CONSTRAINT 名前 で名前を付けておく と、あとで削除するときやエラーメッセージを読むときに役立ちます。次の SQL は MySQL 8.0.46 と PostgreSQL 16.15 の両方でそのまま実行できました。

CREATE TABLE departments (
  dept_id   INT          NOT NULL,
  dept_name VARCHAR(30)  NOT NULL,
  CONSTRAINT pk_departments PRIMARY KEY (dept_id)
);

CREATE TABLE members (
  member_id  INT          NOT NULL,
  email      VARCHAR(100) NOT NULL,
  name       VARCHAR(50)  NOT NULL,
  age        INT,
  status     VARCHAR(10)  DEFAULT 'active' NOT NULL,
  dept_id    INT,
  CONSTRAINT pk_members       PRIMARY KEY (member_id),
  CONSTRAINT uq_members_email UNIQUE (email),
  CONSTRAINT ck_members_age   CHECK (age >= 0),
  CONSTRAINT fk_members_dept  FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
);

INSERT INTO departments VALUES (10, '開発'), (20, '総務');
INSERT INTO members (member_id, email, name, age, dept_id)
VALUES (1, 'sato@example.com', '佐藤', 30, 10);

status を省略して INSERT したので、DEFAULT の 'active' が入っています。

+-----------+------------------+--------+------+--------+---------+
| member_id | email            | name   | age  | status | dept_id |
+-----------+------------------+--------+------+--------+---------+
|         1 | sato@example.com | 佐藤   |   30 | active |      10 |
+-----------+------------------+--------+------+--------+---------+

制約名の付け方に決まりはありませんが、次のように種類がわかる接頭辞をそろえると読みやすくなります。

制約に違反したときのエラーメッセージ一覧

上のテーブルに、わざと制約に違反する INSERT を実行しました。エラーメッセージに制約名が出るので、名前を付けておくとどのルールに当たったかがすぐわかります。

MySQL 8.0.46 の実行結果

PostgreSQL 16.15 の実行結果

PostgreSQL は ERROR の次の行に DETAIL として、当たった値や行の中身を表示します。

Oracle と SQL Server の代表的なエラー番号（未実行）

この表は一般に知られた番号を並べたもので、今回は実行していません。メッセージ全文はお使いの環境で確かめてください。

NULLと制約の関係（つまずきやすい3点）

UNIQUEの列には複数のNULLを入れられる

CREATE TABLE memo (
  id   INT PRIMARY KEY,
  code VARCHAR(10),
  CONSTRAINT uq_memo_code UNIQUE (code)
);
INSERT INTO memo VALUES (1, NULL), (2, NULL), (3, 'A');
SELECT * FROM memo;

+----+------+
| id | code |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | A    |
+----+------+

MySQL 8.0.46 と PostgreSQL 16.15 では、UNIQUE の列に NULL を2行入れられました。 NULL は「値がわからない」という意味なので、NULL どうしは「同じ」と判定されないためです。一方、'A' をもう1行入れると重複エラーになります。

PostgreSQL の NULLS NOT DISTINCT を試した結果です。

CREATE TABLE memo2 (
  id   INT PRIMARY KEY,
  code VARCHAR(10),
  CONSTRAINT uq_memo2 UNIQUE NULLS NOT DISTINCT (code)
);
INSERT INTO memo2 VALUES (1, NULL);
INSERT INTO memo2 VALUES (2, NULL);

ERROR:  duplicate key value violates unique constraint "uq_memo2"
DETAIL:  Key (code)=(null) already exists.

「未入力は許すが、入力されたら重複させない」なら通常の UNIQUE、「必ず入力させて重複もさせない」なら UNIQUE と NOT NULL の両方を付けます。

CHECKと外部キーはNULLを通す

INSERT INTO members (member_id, email, name, age, dept_id)
VALUES (2, 'suzuki@example.com', '鈴木', NULL, NULL);

age には CHECK (age >= 0)、dept_id には外部キーがありますが、どちらも NULL はそのまま登録できました（MySQL・PostgreSQL とも）。CHECK は条件式の結果が「偽」のときだけ拒否し、NULL と比較した結果（不明）は通過させるためです。NULL も受け付けたくない列には、NOT NULL を合わせて付けます。

主キーとUNIQUEの違い

主キーを2つ作ろうとするとエラーになりますが、複数の列を組み合わせた1つの主キー（複合主キー） は作れます。

CREATE TABLE comp_pk (a INT, b INT, PRIMARY KEY (a, b));

ALTER TABLEで制約を追加する（4DB比較）

テーブルを作ったあとに制約を足すときは ALTER TABLE を使います。PRIMARY KEY・UNIQUE・CHECK・FOREIGN KEY は4つの DB でほぼ同じ形、NOT NULL と DEFAULT は DB ごとに書き方が違います。

ポイントを補足します。

• MySQL の MODIFY と SQL Server の ALTER COLUMN は、列の型も一緒に書きます。 型を書き忘れたり、違う型を書いたりすると列の定義が変わるので、SHOW CREATE TABLE などで現在の型を確かめてから実行すると安心です。
• PostgreSQL と Oracle は型を書かずに NOT NULL だけを付け外しできます。
• MySQL の CHECK 制約が実際に働くのは 8.0.16 以降です。それより前の版では構文として受け付けるだけで、検査は行われないことが知られています。
• MySQL では、PRIMARY KEY に CONSTRAINT pk_members と名前を付けても、制約名は常に PRIMARY になります（後述の確認クエリでも PRIMARY と表示されました）。

ALTER TABLEで制約を削除する（4DB比較）

MySQLで実行して確かめたこと

6種類を順に削除し、SHOW CREATE TABLE で確認しました。

ALTER TABLE members DROP FOREIGN KEY fk_members_dept;
ALTER TABLE members DROP CHECK ck_members_age;
ALTER TABLE members DROP INDEX uq_members_email;
ALTER TABLE members MODIFY name VARCHAR(50) NULL;
ALTER TABLE members ALTER COLUMN status DROP DEFAULT;
ALTER TABLE members DROP PRIMARY KEY;
SHOW CREATE TABLE members;

CREATE TABLE `members` (
  `member_id` int NOT NULL,
  `email` varchar(100) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `status` varchar(10) NOT NULL,
  `dept_id` int DEFAULT NULL,
  KEY `fk_members_dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ここから読み取れる MySQL の特徴は3つです。

• 外部キーを削除しても、同じ名前のインデックス（KEY fk_members_dept）は残りました。 不要なら ALTER TABLE members DROP INDEX fk_members_dept; で別に削除します。
• 主キーを削除しても、列の NOT NULL は残りました（member_id int NOT NULL）。
• MySQL の UNIQUE 制約は一意インデックスとして作られるため、DROP INDEX で削除できます。

MySQL 8.0.19 以降では、種類を問わない DROP CONSTRAINT 制約名 も使えます。8.0.46 で UNIQUE・CHECK・FOREIGN KEY の3つは DROP CONSTRAINT で削除できました。主キーだけは名前が PRIMARY に固定されているため、作成時に付けた名前では次のエラーになります。

ALTER TABLE members DROP CONSTRAINT pk_members;

ERROR 3940 (HY000) at line 83: Constraint 'pk_members' does not exist.

MySQL の主キーは DROP PRIMARY KEY で削除する、と覚えておきましょう。

PostgreSQLで実行して確かめたこと

ALTER TABLE members DROP CONSTRAINT fk_members_dept;
ALTER TABLE members DROP CONSTRAINT ck_members_age;
ALTER TABLE members DROP CONSTRAINT uq_members_email;
ALTER TABLE members ALTER COLUMN name DROP NOT NULL;
ALTER TABLE members ALTER COLUMN status DROP DEFAULT;
ALTER TABLE members DROP CONSTRAINT pk_members;

PostgreSQL は PRIMARY KEY・UNIQUE・CHECK・FOREIGN KEY の4つを、すべて DROP CONSTRAINT 制約名 で削除 できます。存在しない制約名を指定したときの挙動は次のとおりでした。

ALTER TABLE members DROP CONSTRAINT pk_members;            -- 2回目
ALTER TABLE members DROP CONSTRAINT IF EXISTS pk_members;

ERROR:  constraint "pk_members" of relation "members" does not exist
NOTICE:  constraint "pk_members" of relation "members" does not exist, skipping

繰り返し実行する移行用スクリプトでは IF EXISTS を付けておくと、2回目以降も止まらずに進みます。制約名だけを変えたいときは、削除せずに次の文で変更できました。

ALTER TABLE members RENAME CONSTRAINT ck_members_age TO ck_members_age_nonneg;

制約名の調べ方

削除には制約名が必要です。名前を付けずに作った場合や、ほかの人が作ったテーブルでは、次の方法で調べます。

MySQL

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 't180' AND TABLE_NAME = 'members';

+------------------+-----------------+----------+
| CONSTRAINT_NAME  | CONSTRAINT_TYPE | ENFORCED |
+------------------+-----------------+----------+
| PRIMARY          | PRIMARY KEY     | YES      |
| uq_members_email | UNIQUE          | YES      |
| fk_members_dept  | FOREIGN KEY     | YES      |
| ck_members_age   | CHECK           | YES      |
+------------------+-----------------+----------+

TABLE_SCHEMA には自分のデータベース名を入れます。CHECK の条件式は information_schema.CHECK_CONSTRAINTS、外部キーの参照先は information_schema.KEY_COLUMN_USAGE で確認できました。

SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 't180' AND TABLE_NAME = 'members';

+------------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME  | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------------+-------------+-----------------------+------------------------+
| PRIMARY          | member_id   | NULL                  | NULL                   |
| uq_members_email | email       | NULL                  | NULL                   |
| fk_members_dept  | dept_id     | departments           | dept_id                |
+------------------+-------------+-----------------------+------------------------+

PostgreSQL

SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'members'::regclass
ORDER BY contype;

     conname      | contype |                      definition                       
------------------+---------+-------------------------------------------------------
 ck_members_age   | c       | CHECK ((age >= 0))
 fk_members_dept  | f       | FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
 pk_members       | p       | PRIMARY KEY (member_id)
 uq_members_email | u       | UNIQUE (email)

contype は c が CHECK、f が外部キー、p が主キー、u が UNIQUE です。psql を使っているなら \d members でも、列の NOT NULL・DEFAULT と合わせて一覧できます。

Indexes:
    "pk_members" PRIMARY KEY, btree (member_id)
    "uq_members_email" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "ck_members_age" CHECK (age >= 0)
Foreign-key constraints:
    "fk_members_dept" FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

なお、PostgreSQL 16.15 で information_schema.table_constraints を見ると、NOT NULL が 2200_180384_1_not_null のような名前の CHECK として4行表示されました。自分で付けた制約だけを見たいときは pg_constraint のほうが読みやすいです。

名前を付けなかったときに自動で付く名前

-- MySQL
CREATE TABLE auto_name (
  id INT PRIMARY KEY,
  code VARCHAR(10) UNIQUE,
  qty INT CHECK (qty > 0),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
);

（PostgreSQL 側は dept_id INT REFERENCES departments (dept_id) と列に直接書いて確認しました。）Oracle では SYS_C で始まる連番、SQL Server では PK__テーブル名__英数字 のような名前が自動で付くことが知られています（未実行）。環境ごとに違う名前になると削除用の SQL を共通化しにくいので、最初から名前を付けておくのがおすすめです。

既存データが違反していて制約を追加できないときの対処

運用中のテーブルに制約を足そうとすると、すでに入っているデータが条件に合わず、追加そのものがエラーになることがあります。制約を外した状態で、わざと違反する行を入れて再現しました。

-- 重複した member_id・email、name が NULL、age が負、存在しない dept_id を持つ行
INSERT INTO members (member_id, email, name, age, status, dept_id)
VALUES (1, 'sato@example.com', NULL, -5, 'x', 99);

追加時のエラーメッセージ

MySQL の外部キーのメッセージでは、テーブル名が #sql-af8_e という内部の作業用の名前で表示されました。自分のテーブル名と違って見えますが、原因は「親テーブルにない値がある」ことです。

手順1：違反している行を探す

エラーが出たら、まず どの行が条件に合っていないのかを SELECT で見つけます。 次のクエリは MySQL・PostgreSQL 共通です。

-- 主キー・UNIQUE にしたい列の重複
SELECT member_id, COUNT(*) FROM members GROUP BY member_id HAVING COUNT(*) > 1;
SELECT email, COUNT(*) FROM members GROUP BY email HAVING COUNT(*) > 1;

-- NOT NULL にしたい列の NULL
SELECT * FROM members WHERE name IS NULL;

-- CHECK の条件を満たさない行
SELECT * FROM members WHERE NOT (age >= 0);

-- 外部キー：親テーブルに存在しない値
SELECT m.*
FROM members m
LEFT JOIN departments d ON d.dept_id = m.dept_id
WHERE m.dept_id IS NOT NULL AND d.dept_id IS NULL;

MySQL 8.0.46 では、重複の2クエリが 1 / 2件 と sato@example.com / 2件 を返し、残りの3クエリはいずれも先ほど入れた1行を返しました。

+-----------+------------------+------+------+--------+---------+
| member_id | email            | name | age  | status | dept_id |
+-----------+------------------+------+------+--------+---------+
|         1 | sato@example.com | NULL |   -5 | x      |      99 |
+-----------+------------------+------+------+--------+---------+

手順2：データを直してから追加する

今回は違反行を削除してから追加し直し、MySQL・PostgreSQL ともに5つすべてがエラーなく追加できることを確認しました。

DELETE FROM members WHERE name IS NULL;
ALTER TABLE members ADD CONSTRAINT pk_members PRIMARY KEY (member_id);
ALTER TABLE members ADD CONSTRAINT uq_members_email UNIQUE (email);
-- 以下、NOT NULL・CHECK・FOREIGN KEY・DEFAULT も同様に追加

手順3：すぐに直せないときは「新しい行だけ先に検査する」

データの修正に時間がかかるときは、これから入るデータにだけ先に制約を効かせ、既存データはあとで確認する という進め方があります。

PostgreSQL：NOT VALID で追加し、あとから VALIDATE CONSTRAINT（CHECK と FOREIGN KEY が対象）。

ALTER TABLE members ADD CONSTRAINT ck_members_age CHECK (age >= 0) NOT VALID;
ALTER TABLE members ADD CONSTRAINT fk_members_dept
  FOREIGN KEY (dept_id) REFERENCES departments (dept_id) NOT VALID;

-- 新しい行はすぐに検査される
INSERT INTO members (member_id, email, name, age, status, dept_id)
VALUES (3, 'kato@example.com', '加藤', -1, 'active', 10);

ERROR:  new row for relation "members" violates check constraint "ck_members_age"
DETAIL:  Failing row contains (3, kato@example.com, 加藤, -1, active, 10).

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'members'::regclass;

     conname     | convalidated 
-----------------+--------------
 ck_members_age  | f
 fk_members_dept | f

convalidated が f（未検証）になっています。既存データを直してから検証すると t に変わりました。違反行が残っている間は、検証もエラーになります。

ALTER TABLE members VALIDATE CONSTRAINT ck_members_age;
-- 違反行があるとき → ERROR:  check constraint "ck_members_age" of relation "members" is violated by some row
-- 直したあと     → ALTER TABLE

MySQL：CHECK を NOT ENFORCED（検査しない状態）で追加し、あとから ENFORCED に切り替える。

ALTER TABLE members ADD CONSTRAINT ck_members_age CHECK (age >= 0) NOT ENFORCED;
-- データを直したあとで有効にする
ALTER TABLE members ALTER CHECK ck_members_age ENFORCED;

MySQL 8.0.46 では、違反行が残ったまま ENFORCED にしようとすると ERROR 3819 (HY000): Check constraint 'ck_members_age' is violated. になり、違反行を直したあとは切り替えられました。NOT ENFORCED の間は新しい行も検査されない点が、PostgreSQL の NOT VALID との違いです。

主キーと UNIQUE には、この「あと回し」は基本的に使えません（重複があると一意インデックスを作れないため）。先に重複を解消してから追加します。

外部キーの親を削除・更新したときの動き（ON DELETE）

外部キーは、親の行を消そうとしたときの動きも指定できます。指定しないときは、今回確認したとおり削除がエラーになります。

PostgreSQL 16.15 で ON DELETE SET NULL に付け替えて確かめました。1つの ALTER TABLE に DROP と ADD を並べると、制約の入れ替えを1文で行えます。

ALTER TABLE members
  DROP CONSTRAINT fk_members_dept,
  ADD CONSTRAINT fk_members_dept FOREIGN KEY (dept_id)
      REFERENCES departments (dept_id) ON DELETE SET NULL;

DELETE FROM departments WHERE dept_id = 10;
SELECT member_id, name, dept_id FROM members ORDER BY 1;

 member_id | name | dept_id 
-----------+------+---------
         1 | 佐藤 |        
         2 | 鈴木 |        

CASCADE は便利な一方、親を1行消すと関連する子がまとめて消えます。どのテーブルまで影響するかを確かめてから指定しましょう。

MySQLのsql_modeとNOT NULL

MySQL では、sql_mode に STRICT_TRANS_TABLES が含まれているかどうかで、NOT NULL 列を省略したときの動きが変わります。8.0.46 の既定値には含まれていました。試しに sql_mode を空にして実行した結果です。

SET SESSION sql_mode = '';
INSERT INTO members (member_id, email, age) VALUES (50, 'x@example.com', 20);
SHOW WARNINGS;

+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

エラーではなく警告になり、name には空文字が入りました。 NOT NULL を付けているのに空の値が入っているときは、SELECT @@sql_mode; で設定を確かめてみてください。

制約の設計チェックリスト

よくある質問

Q. 主キーと UNIQUE はどう使い分けますか？ A. 行を特定するための ID には主キー（1テーブルに1つ、NULL 不可）、ID ではないけれど重複させたくない列には UNIQUE（いくつでも、NULL 可）を使います。UNIQUE の列で未入力も受け付けたくないときは NOT NULL を合わせて付けます。

Q. 制約を一時的に無効にできますか？ A. DB によって方法があります。MySQL は CHECK を ALTER CHECK 制約名 NOT ENFORCED で検査しない状態にできます。PostgreSQL は NOT VALID で既存データの検査をあと回しにできます。大量データの取り込みなどで外す場合は、取り込み後に違反行がないことを確認してから元に戻す流れにしましょう。

Q. 制約名がわからないときはどうすればよいですか？ A. MySQL は SHOW CREATE TABLE テーブル名;、PostgreSQL は psql の \d テーブル名 が一番手軽です。SQL で調べるなら、MySQL は information_schema.TABLE_CONSTRAINTS、PostgreSQL は pg_constraint を参照します。

Q. NOT NULL を追加しようとしたらエラーになりました。 A. その列にすでに NULL の行があります。MySQL では ERROR 1138: Invalid use of NULL value、PostgreSQL では column "…" contains null values と表示されました。SELECT * FROM テーブル WHERE 列 IS NULL; で対象を確認し、UPDATE で値を入れてから追加し直します。

まとめ

• 制約は6種類：PRIMARY KEY・UNIQUE・NOT NULL・CHECK・FOREIGN KEY・DEFAULT。主キーは1テーブルに1つ、ほかは複数付けられる。
• PRIMARY KEY・UNIQUE・CHECK・FOREIGN KEY の追加は ALTER TABLE ... ADD CONSTRAINT 名前 ... で4つの DB 共通。
• NOT NULL と DEFAULT は DB で書き方が違う（MySQL・Oracle は MODIFY、PostgreSQL・SQL Server は ALTER COLUMN）。
• 削除は DROP CONSTRAINT 制約名 が基本。MySQL は DROP PRIMARY KEY・DROP INDEX・DROP FOREIGN KEY・DROP CHECK も使う。
• UNIQUE・CHECK・外部キーは NULL を通す。NULL も受け付けたくない列には NOT NULL を合わせて付ける。
• 追加できないときは、違反行を SELECT で探して直してから追加する。PostgreSQL の NOT VALID、MySQL の NOT ENFORCED で段階的に進める方法もある。
• 制約には名前を付ける。エラーメッセージが読みやすくなり、削除用の SQL も環境を問わず同じになる。

株式会社RJCでは、SQLの制約やテーブル設計の考え方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。